Imports System.IO
'Imports Microsoft.Data.Schema.ScriptDom
'Imports Microsoft.Data.Schema.ScriptDom.Sql

''' <summary>
''' Base class for all data providers
''' </summary>
''' <remarks>
''' The named data providers listed in enumDBType have specific formatting requirements
''' When these can be handled in a way that does not require the provider's library to be 
''' referenced (e.g., OleDB), then SELECT CASE statements will usually be used in this library
''' to reduce the number of overridden methods required
''' </remarks>
Public MustInherit Class clsDB
    Implements IDisposable


#Region "IDisposable Support"
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose( disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

    ''' <summary>Name of temporary database used by some providers so can set project database readonly and yet create temporary tables</summary>
    ''' <remarks>NOTE: ASSUMES THAT CLSPROJECT.TEMPDBNAME IS "TempDB"!!!</remarks>
    Protected TempDBName As String = "TempDB"

    Public LastException As Exception = Nothing

    'following datatables store database schema information; they were made persistent because the GetSchema call is really slow
    'whenever one of the collections would be changed (e.g., delete a table), should reset the table to Nothing to force a refresh
    Private Schema_Tables As DataTable = Nothing
    Private Schema_Views As DataTable = Nothing
    Private Schema_Columns As DataTable = Nothing
    Private Schema_IndexColumns As DataTable = Nothing
    Private Schema_Indexes As DataTable = Nothing

    Friend Const ParadoxMessage As String = "An error occurred while executing a query. This may be caused by a corrupted table; you should be able to rebuild the table using File | File Management | Explore Database | Compact/Repair." & vbCr & vbCr & "Another possible problem could be that you are logged in as a limited (non-Administrator) user, and the Borland Database Engine is not properly configured. See the WRDB help topic 'Paradox Data Provider'"

#Region "Routines to test and process standard SQL strings"

    ''' <summary>
    ''' Begin transaction grouping following delete, update, and insert commands (automatically called by those routines if not done here); do not include select and count queries in same transaction as insert/update/delete queries (SQLite limitation)
    ''' </summary>
    ''' <remarks></remarks>
    Public Function BeginTransaction() As DbTransaction
        OpenConnection()
        Return Connection.BeginTransaction
    End Function

    Public Enum enumEndTransaction
        None
        Commit
        Rollback
    End Enum

    ''' <summary>
    ''' End prior transaction, either committing or rolling all operations back
    ''' </summary>
    ''' <param name="Trans">Transaction object created with BeginTransaction</param>
    ''' <param name="EndTran">What to do with transaction operations</param>
    ''' <remarks></remarks>
    Public Sub EndTransaction(ByRef Trans As DbTransaction, EndTran As enumEndTransaction)
        If Trans Is Nothing Then Exit Sub
        If Trans Is Nothing And EndTran <> enumEndTransaction.None Then Throw New Exception("EndTransaction called before BeginTransaction")
        Select Case EndTran
            Case enumEndTransaction.Commit
                Trans.Commit()
            Case enumEndTransaction.Rollback
                Trans.Rollback()
            Case enumEndTransaction.None
                'don't to anything
        End Select
        If Trans IsNot Nothing Then Trans.Dispose()
        Trans = Nothing
    End Sub

    ''' <summary>
    ''' Execute a non-query against the database; if connection is closed, will open it; WILL close connection automatically; returns -2 if error occurs
    ''' </summary>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <returns>Number of affected records or -2 if error occurred</returns>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Function ExecuteNonQuery(SqlString As String) As Integer
        If Not OpenConnection() Then Return False
        Dim cmd As DbCommand
        cmd = dbConnection.CreateCommand

        If SqlString.Contains("JOIN") Then
            'some queries that use Joins (like for reports) require parentheses if they are Jet databases but will cause others (SQLite) to fail
            'the parens should be in place before this routine is called; and they will be removed here
            Select Case dbType
                Case enumDBType.SQLite
                    SqlString = SqlString.Replace("FROM (((", "FROM ")
                    SqlString = SqlString.Replace("FROM ((", "FROM ")
                    SqlString = SqlString.Replace("FROM (", "FROM ")
                    SqlString = SqlString.Replace(") LEFT", " LEFT")
                    SqlString = SqlString.Replace(") GROUP", " GROUP")
                    SqlString = SqlString.Replace(") WHERE", " WHERE")
                    SqlString = SqlString.Replace(") ORDER", " ORDER")
            End Select
        End If

        'if embedded returns codes (\n) are included, replace them here
        cmd.CommandText = SqlString.Replace("\n", vbCr).Replace("\N", vbCr)
        cmd.CommandTimeout = CommandTimeout
        LastException = Nothing

        Try
            Dim ret As Integer = cmd.ExecuteNonQuery
            If SqlString.ToUpper.StartsWith("CREATE VIEW") Then Schema_Views = Nothing
            If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
            Return ret
        Catch ex As DbException
            LastException = ex
            ExecuteNonQuery = -2
            If Me.dbType = enumDBType.Paradox Then
                Throw New DbSqlException(ParadoxMessage, SqlString, ex)
            Else
                Throw New DbSqlException(ex.Message, SqlString, ex)
            End If
        Catch ex As Exception
            LastException = ex
            'ErrorMsg("Unable to execute query.", cmd.CommandText, ex)
            'Return "An error occurred executing the query. The SQL string was:\n\n" & cmd.CommandText & "\n\nThe detailed error message was:\n\n" & ex.ToString
            Return -2
        Finally
            cmd.Dispose()
            CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Execute a non-query against the database; if connection is closed, will open it; WILL close connection automatically
    ''' </summary>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Number of affected records or -2 if error occurred</returns>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Function ExecuteNonQuery(SqlStringFormat As String, ParamArray Args() As Object) As Integer
        Dim sql As String = String.Format(SqlStringFormat, Args)
        Return ExecuteNonQuery(sql)
    End Function

    ''' <summary>
    ''' Execute a non-query against the database; if connection is closed, will open it; will NOT close connection automatically
    ''' This routine is used for multiple command execution
    ''' </summary>
    ''' <param name="Transaction">Transaction object</param>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Number of affected records or -2 if error occurred</returns>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function ExecuteNonQuery(ByRef Transaction As DbTransaction, SqlStringFormat As String, ParamArray Args() As Object) As Integer
        Return ExecuteNonQuery(Transaction, String.Format(SqlStringFormat, Args))
    End Function

    ''' <summary>
    ''' Execute a non-query against the database; if connection is closed, will open it; will NOT close connection automatically
    ''' This routine is used for multiple command execution
    ''' </summary>
    ''' <param name="Transaction">Transaction object</param>
    ''' <param name="SqlString">Format of standard sql string</param>
    ''' <returns>Number of affected records or -2 if error occurred</returns>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function ExecuteNonQuery(ByRef Transaction As DbTransaction, SqlString As String) As Integer
        If Not OpenConnection() Then Return False
        Dim cmd As DbCommand
        cmd = dbConnection.CreateCommand
        cmd.Transaction = Transaction
        cmd.CommandText = SqlString
        cmd.CommandTimeout = CommandTimeout
        LastException = Nothing
        Try
            Dim ret As Integer = cmd.ExecuteNonQuery
            If SqlString.ToUpper.StartsWith("CREATE VIEW") Then Schema_Views = Nothing
            Return ret
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException(ex.Message, SqlString, ex)
        Finally
            cmd.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' Executes sql query and returns a fast reader
    ''' </summary>
    ''' <param name="Trans">Transaction object</param>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <param name="CmdBehavior">Optional behavior parameter</param>
    ''' <returns>Data Reader</returns>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Overridable Function ExecuteReader(ByRef Trans As DbTransaction, SqlString As String, Optional CmdBehavior As CommandBehavior = CommandBehavior.Default) As DbDataReader
        If dbConnection Is Nothing Then Return Nothing

        Dim cmd As DbCommand
        If Not OpenConnection() Then Return Nothing
        cmd = dbConnection.CreateCommand
        LastException = Nothing

        With cmd
            If Trans IsNot Nothing Then .Transaction = Trans
            .CommandText = SqlString.Replace("\n", vbCr)
            If dbTimeoutOverride <> 0 And dbType <> enumDBType.SQL_Server_CE Then
                .CommandTimeout = dbTimeoutOverride
                dbTimeoutOverride = 0 'only valid for a single query (so caller doesn't have to reset)
            Else
                .CommandTimeout = CommandTimeout
            End If
tryagain:
            Try
                Dim rdr As DbDataReader = .ExecuteReader(CmdBehavior)
                Return rdr
            Catch ex As DbException
                If Me.dbType = enumDBType.Paradox Then
                    Throw New DbSqlException(ParadoxMessage, SqlString, ex)
                Else
                    Throw New DbSqlException(ex.Message, SqlString, ex)
                End If
                Return Nothing
            Catch ex As Exception
                LastException = ex
                Throw
                Return Nothing
            Finally
                'cmd.Dispose()
            End Try
        End With
    End Function

    ''' <summary>
    ''' Executes sql query and returns a fast reader
    ''' </summary>
    ''' <param name="Trans">Transaction object</param>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Data Reader</returns>
    Public Function ExecuteReader(ByRef Trans As DbTransaction, SqlStringFormat As String, ParamArray Args() As Object) As DbDataReader
        Return ExecuteReader(Trans, String.Format(SqlStringFormat, Args))
    End Function

    ''' <summary>
    ''' Executes sql query and returns a fast reader
    ''' </summary>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <param name="CmdBehavior">Optional behavior parameter</param>
    ''' <returns>Data Reader</returns>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Function ExecuteReader(SqlString As String, Optional CmdBehavior As CommandBehavior = CommandBehavior.Default) As DbDataReader
        Return ExecuteReader(Nothing, SqlString, CmdBehavior)
    End Function

    ''' <summary>
    ''' Executes sql query and returns a fast reader
    ''' </summary>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Data Reader</returns>
    Public Function ExecuteReader(SqlStringFormat As String, ParamArray Args() As Object) As DbDataReader
        Dim sql As String = String.Format(SqlStringFormat, Args)
        Return ExecuteReader(sql)
    End Function

    ''' <summary>
    ''' Executes sql query that returns a single value; WILL NOT close connection automatically
    ''' </summary>
    ''' <param name="Trans">Transaction object (if Nothing, connection will be closed)</param>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <returns>Result of sql query as object</returns>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Function ExecuteScalar(ByRef Trans As DbTransaction, SqlString As String) As Object
        Try
            If dbConnection Is Nothing Then Return Nothing

            If Not OpenConnection() Then Return Nothing
            Dim cmd As DbCommand = dbConnection.CreateCommand
            LastException = Nothing

            With cmd
                .CommandText = SqlString.Replace("\n", vbCr)
                If Trans IsNot Nothing Then .Transaction = Trans

                If dbTimeoutOverride <> 0 And dbType <> enumDBType.SQL_Server_CE Then
                    .CommandTimeout = dbTimeoutOverride
                    dbTimeoutOverride = 0 'only valid for a single query (so caller doesn't have to reset)
                Else
                    .CommandTimeout = CommandTimeout
                End If

                Dim o As Object

                Try
                    o = .ExecuteScalar
                    Return o
                Catch ex As DbException
                    LastException = ex
                    If Me.dbType = enumDBType.Paradox Then
                        Throw New DbSqlException(ParadoxMessage, SqlString, ex)
                    Else
                        Throw New DbSqlException(ex.Message, SqlString, ex)
                    End If
                    Return Nothing
                Catch ex As Exception
                    LastException = ex
                    Throw
                    Return Nothing
                Finally
                    .Dispose()
                    If Trans Is Nothing Then dbConnection.Close()
                End Try
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Executes sql query that returns a single value; WILL NOT close connection automatically
    ''' </summary>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Result of sql query as object</returns>
    Public Function ExecuteScalar(ByRef Trans As DbTransaction, SqlStringFormat As String, ParamArray Args() As Object) As Object
        Return ExecuteScalar(Trans, String.Format(SqlStringFormat, Args))
    End Function

    ''' <summary>
    ''' Executes sql query that returns a single value; WILL close connection automatically
    ''' </summary>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <returns>Result of sql query as object</returns>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function ExecuteScalar(SqlString As String) As Object
        Return ExecuteScalar(Nothing, SqlString)
    End Function

    ''' <summary>
    ''' Executes sql query that returns a single value; WILL close connection automatically
    ''' </summary>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>Result of sql query as object</returns>
    Public Function ExecuteScalar(SqlStringFormat As String, ParamArray Args() As Object) As Object
        Dim sql As String = String.Format(SqlStringFormat, Args)
        Return ExecuteScalar(sql)
    End Function

    ''' <summary>
    ''' Test SQL string to see if the syntax is valid
    ''' </summary>
    ''' <param name="SqlString">Standard sql string</param>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function IsSqlValid(SqlString As String) As Boolean
        If SqlString.StartsWith("CREATE", StringComparison.OrdinalIgnoreCase) Or _
           SqlString.StartsWith("ALTER", StringComparison.OrdinalIgnoreCase) Or _
           SqlString.StartsWith("DROP", StringComparison.OrdinalIgnoreCase) Or _
           SqlString.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase) Then Return True 'cannot test
        If Not OpenConnection() Then Return False
        Dim cmd As DbCommand
        cmd = dbConnection.CreateCommand
        SqlString = NullSQLString(SqlString)
        cmd.CommandText = SqlString.Replace("\N", vbCr).Replace("\n", vbCr)
        LastException = Nothing
        Try
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            LastException = ex
            Return False
        Finally
            cmd.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' Test SQL string to see if the syntax is valid
    ''' </summary>
    ''' <param name="SqlStringFormat">Format of standard sql string</param>
    ''' <param name="Args">Format argument</param>
    ''' <returns>True if SQL string is valid, false otherwise</returns>
    Public Function IsSqlValid(SqlStringFormat As String, ParamArray Args() As Object) As Boolean
        Return IsSqlValid(String.Format(SqlStringFormat, Args))
    End Function

    ''' <summary>
    ''' Given a SQL string, change WHERE clause so that it will return no records and thus run very fast
    ''' </summary>
    ''' <param name="SqlStringFormat">Sql string (or string format statement used to build sql string)</param>
    ''' <param name="Args">Arguments used to build sql string</param>
    ''' <returns></returns>
    ''' <remarks>Useful to test validity of SQL statement or to create datatable with correct structure but no records</remarks>
    Private Function NullSQLString(SqlStringFormat As String, ParamArray Args() As String) As String
        Dim SqlString As String = String.Format(SqlStringFormat, Args)
        'SqlString = SqlString.ToUpper
        If SqlString.ToUpper.Contains("WHERE ") Then 'need to replace *last* where in case compound select
            Dim i As Integer = SqlString.ToUpper.LastIndexOf("WHERE ")
            SqlString = SqlString.Insert(i + 6, "'A'='B' AND ")
        Else
            If SqlString.ToUpper.Contains("GROUP BY ") Then
                'SqlString = SqlString.Replace("GROUP BY ", " WHERE 'A'='B' GROUP BY ")
                SqlString = Text.RegularExpressions.Regex.Replace(SqlString, "GROUP BY ", " WHERE 'A'='B' GROUP BY ", Text.RegularExpressions.RegexOptions.IgnoreCase)
            ElseIf SqlString.ToUpper.Contains(" ORDER BY ") AndAlso Not SqlString.Contains("OVER") Then 'note: could cause problem if do ROW_NUMBER() OVER (ORDER BY...
                'SqlString = SqlString.Replace("ORDER BY ", " WHERE 'A'='B' ORDER BY ")
                SqlString = Text.RegularExpressions.Regex.Replace(SqlString, "ORDER BY ", " WHERE 'A'='B' ORDER BY ", Text.RegularExpressions.RegexOptions.IgnoreCase)
            ElseIf SqlString.ToUpper.Contains(" LIMIT ") Then
                'SqlString = SqlString.Replace("LIMIT ", " WHERE 'A'='B' LIMIT ")
                SqlString = Text.RegularExpressions.Regex.Replace(SqlString, " LIMIT ", " WHERE 'A'='B' LIMIT ", Text.RegularExpressions.RegexOptions.IgnoreCase)
            Else
                SqlString &= " WHERE 'A'='B'"
            End If
        End If
        Return SqlString
    End Function

#End Region

#Region "Shared methods used for provider discovery"


    Public Shared DataProviderName() = {"DBParadox", "DBAccess", "DBSQLServer", "DBSQLServerCE", "DBOracle", "DBFirebird", "DBFirebird", "DBMySQL", "DBSQLite"}

    Private Shared _DLLPath As String = ""

    ''' <summary>
    ''' Get path to all WRDB DLLs so can dynamically load appropriate library (does not end in trailing \)
    ''' </summary>
    ''' <remarks>If ASP.Net, must set to Server.MapPath("~\bin") before using rest of routine</remarks>
    Public Shared Property DLLPath As String
        Get
            If _DLLPath = "" Then
                My.User.InitializeWithWindowsUser()
                If False And Debugger.IsAttached And My.User.Name.Contains("LCW") Then
                    _DLLPath = String.Format("D:\Projects\WRDB {0}\bin", My.Application.Info.Version.ToString(2))
                Else
                    _DLLPath = GetSetting(REGAPP, "Defaults", String.Format("DLL Path {0}", My.Application.Info.Version.ToString(2)), My.Application.Info.DirectoryPath)
                End If
                If Not My.Computer.FileSystem.DirectoryExists(_DLLPath) Then
                    Throw New IO.DirectoryNotFoundException("The directory containing the WRDB program was not found; please make sure that WRDB is installed and run it at least one time to initialize the system so that it may be found by other applications.")
                    _DLLPath = ""
                End If
            End If
            Return _DLLPath
        End Get
        Set(value As String)
            _DLLPath = value
        End Set
    End Property

    ''' <summary>
    ''' Returns a list of generic data provider names contained in the RootPath folder (or subfolder below it)
    ''' Note that the DLLs must inherit DB.dll and thus will contain a class called clsDB; the filename must be
    ''' formatted as DBxxx.dll; the namespace must also be a matching DBxxx; if the DLL directory cannot be found,
    ''' will use the current appliation's directory
    ''' 5/6/11: rolled all providers into a single library, so just return all providers
    ''' </summary>
    ''' <returns>List of provider types</returns>
    Public Shared Function EnumDataProviders() As Generic.List(Of enumDBType)
        Dim lst As New Generic.List(Of enumDBType)
        lst.AddRange(New enumDBType() {enumDBType.Access, enumDBType.CSV, enumDBType.DBase, enumDBType.Excel, enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.MySQL, enumDBType.Oracle, enumDBType.Paradox, enumDBType.SQL_Server, enumDBType.SQL_Server_CE, enumDBType.SQLite, enumDBType.Text})
        Return lst



        'Try
        '    If DLLPath = "" Then DLLPath = My.Application.Info.DirectoryPath
        'Catch ex As Exception
        '    'don't catch this error when enumerating data providers because is OK to look in current application's path too
        '    DLLPath = My.Application.Info.DirectoryPath
        'End Try

        'For Each f As String In My.Computer.FileSystem.GetFiles(DLLPath, FileIO.SearchOption.SearchTopLevelOnly, "DB*.dll")
        '    Dim assemblyObj As Reflection.Assembly = Nothing
        '    Try
        '        assemblyObj = Reflection.Assembly.LoadFrom(f)
        '        For Each t As System.Type In assemblyObj.GetTypes()
        '            If t.Name = "clsDB" And Not IO.Path.GetFileNameWithoutExtension(f).Equals("DBBase", StringComparison.OrdinalIgnoreCase) Then
        '                lst.Add(Array.IndexOf(DataProviderName, assemblyObj.GetName.Name))
        '            End If
        '        Next
        '    Catch ex As Exception
        '        'NOTE: this error may occur when application is compiled for AnyCPU and it fails when loading a 32 Bit provider on a 64-bit machine
        '        'For the time being, must compile main application for x86 so 64-bit versions aren't requested
        '        Throw New IO.FileLoadException("Unable to load assembly from file: " & f, ex)
        '    End Try
        'Next
        'If lst.Contains(enumDBType.Firebird) Then lst.Add(enumDBType.Firebird_Embedded) 'they share the same libraries
        'lst.Add(enumDBType.DBase)
        'lst.Add(enumDBType.Excel)
        'lst.Add(enumDBType.Text)
        'lst.Add(enumDBType.CSV)
        'Return lst
    End Function

    ''' <summary>
    ''' Given valid name of data provider (see GetDataProviders), will create an instance of it and assign it to the baseclass name
    ''' </summary>
    ''' <param name="DatabaseType">Enumerated type of provider</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDataProvider(DatabaseType As enumDBType, _
                    Optional DataSource As String = "", _
                    Optional DatabaseName As String = "", _
                    Optional UserName As String = "", _
                    Optional Password As String = "", _
                    Optional Timeout As Integer = 30) As clsDB
        If Not EnumDataProviders().Contains(DatabaseType) Then
            Throw New DbSqlException(String.Format("The requested data provider ({0}) could not be found on the requested path ({1}); please select a different provider or place the required library in the path.", DatabaseType.ToString, DLLPath.Replace("\", "\\")))
            Return Nothing
        End If
        Dim DB As clsDB = Nothing
        Select Case DatabaseType
            Case enumDBType.Access : DB = New clsDBAccess
            Case enumDBType.CSV : DB = New clsDBCsv
            Case enumDBType.DBase : DB = New clsDBDBase
            Case enumDBType.Excel : DB = New clsDBExcel
            Case enumDBType.Firebird : DB = New clsDBFirebird
            Case enumDBType.Firebird_Embedded : DB = New clsDBFirebird
            Case enumDBType.MySQL : DB = New clsDBMySQL
            Case enumDBType.Oracle : DB = New clsDBOracle
            Case enumDBType.Paradox : DB = New clsDBParadox
            Case enumDBType.SQL_Server : DB = New clsDBSQLServer
            Case enumDBType.SQL_Server_CE : DB = New clsDBSQLServerCE
            Case enumDBType.Text : DB = New clsDBText
            Case enumDBType.SQLite : DB = New clsDBSQLite
        End Select

        'some providers won't work in 64-bits--trap that here
        If Is64BitProcess() Then
            Select Case DatabaseType
                Case enumDBType.Access
                    Throw New DbSqlException("You are running WRDB as a 64-bit application, however the selected data provider is not supported in this mode. Please use a different data provider or the 32-bit version of WRDB.")
                    Return Nothing
            End Select
        End If

        With DB
            .DatabaseType = DatabaseType
            .DataSource = DataSource
            .DatabaseName = DatabaseName
            .UserName = UserName
            .Password = Password
            .CommandTimeout = Timeout
            If DataSource <> "" Then .CreateConnection()
        End With
        Return DB
    End Function
#End Region

    Public Enum enumColType
        [String]
        [Double]
        [Integer]
        [Boolean]
        [Date]
    End Enum

    ''' <summary>
    ''' List of data providers supported by class
    ''' </summary>
    ''' <remarks>Only a subset of these are extensively tested</remarks>
    Public Enum enumDBType

        ''' <summary>Corel/Borland Paradox file-based database (using Jet)</summary>
        Paradox

        ''' <summary>Microsoft Access (mdb) embedded file-based database (using Jet)</summary>
        ''' <remarks>Provided for backwards compatibility, however performance of Jet engine is rather poor</remarks>
        Access

        ''' <summary>Microsoft SQLServer client-server databases including MSDE, SQL Express, etc.</summary>
        ''' <remarks>Does NOT SQL_Server_CE support which doesn't provide SCHEMA commands, etc.</remarks>
        SQL_Server

        ''' <summary>Microsoft SQLServer Compact Edition 3.5</summary>
        SQL_Server_CE

        ''' <summary>Oracle client-server database</summary>
        Oracle

        ''' <summary>Firebird open-source database (www.firebirdsql.org)--client-server version</summary>
        Firebird

        ''' <summary>Firebird open-source database (www.firebirdsql.org)--embedded file version</summary>
        ''' <remarks>
        ''' Requires embedded firebird engine 2.0 and .net provider (www.sourceforge.net). According to Readme notes:
        ''' Just copy fbembed.dll, icudt30.dll, icuin30.dll and icuuc30.dll into the directory with your application.
        ''' Then rename fbembed.dll to either fbclient.dll or gds32.dll depending on your database connectivity software.
        ''' Then start your application and it will use the embedded server as a client library and will be able to access
        ''' local datasases. You should also copy firebird.msg and firebird.conf (if necessary) to the same directory.
        ''' 
        ''' In firebird.conf must set ExternalFileAccess = Full
        ''' </remarks>
        Firebird_Embedded

        ''' <summary>MySQL open-source client-server database (www.mysql.com)</summary>
        MySQL

        ''' <summary>SQLite open-source embedded database (www.sqlite.org)</summary>
        SQLite

        DBase
        Excel
        Text
        CSV

    End Enum

    ''' <summary>
    ''' Is raised during long operations (e.g., import/export) so caller can report using UI
    ''' </summary>
    ''' <param name="Value">Current value (must alway initialize with 0 and finalize with Value=MaxValue)</param>
    ''' <param name="MaxValue">Maximum value</param>
    ''' <param name="ProgressText">Text to display</param>
    Public Event Progress(ByRef DB As clsDB, Value As Long, MaxValue As Long, ProgressText As String)

    ''' <summary>
    ''' Background object for queries so UI doesn't appear to freeze
    ''' </summary>
    Private WithEvents bwAddTable As System.ComponentModel.BackgroundWorker

    Private bwAddTableDataAdapter As DbDataAdapter

    ''' <summary>
    ''' Background object for queries so UI doesn't appear to freeze
    ''' </summary>
    Private WithEvents bwExecuteNonQuery As System.ComponentModel.BackgroundWorker

    ''' <summary>
    ''' Background object for queries so UI doesn't appear to freeze
    ''' </summary>
    Private WithEvents bwExecuteScalar As System.ComponentModel.BackgroundWorker

    'following are used to allow interruption of DataAdapter Fill operation

    Dim db_da As DbDataAdapter = Nothing 'generic data adapter

    ''' <summary>
    ''' Set to true if caller wants to cancel long operation
    ''' </summary>
    Private dbCancelled As Boolean

    ''' <summary>
    ''' Database connection object
    ''' </summary>
    Private dbConnection As DbConnection

    ''' <summary>
    ''' Name of database on server
    ''' </summary>
    ''' <remarks>Not used for embedded databases</remarks>
    Private dbDatabaseName As String

    ''' <summary>
    ''' String containing name of database file (for embedded databases) or server name (or IP address)
    ''' </summary>
    Private dbDataSource As String

    ''' <summary>Password for protected databases</summary>
    Private dbPassword As String

    ''' <summary>Length of time (sec) for command timeout</summary>
    Private dbTimeout As Integer

    ''' <summary>
    ''' If non-zero, will use this timeout value to execute command
    ''' </summary>
    Private dbTimeoutOverride As Integer

    ''' <summary>
    ''' Database type
    ''' </summary>
    Private dbType As enumDBType

    ''' <summary>User name for protected databases</summary>
    Private dbUserName As String

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ''' <summary>
    ''' Used to fill datatable on background thread so UI doesn't appear to freeze
    ''' </summary>
    Dim WithEvents dt As DataTable

    ''' <summary>
    ''' Can be used to prevent multiple queries from being run in background
    ''' </summary>
    Public IsQueryBusy As Boolean

    ''' <summary>
    ''' Last query string; used to report errors in background thread
    ''' </summary>
    Private QuerySQL As String

    ''' <summary>
    ''' Adds new table to existing dataset containing records from sql query; 
    ''' uses threading so progress dialog doesn't appear to freeze during long query
    ''' </summary>
    ''' <param name="DataSet">Existing or new dataset</param>
    ''' <param name="TableName">Name of table to create or overwrite in dataset</param>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <param name="Args">Optional arguments when SqlString contains formatting codes</param>
    ''' <remarks>Connection is automatically opened and closed</remarks>
    Public Function AddTable(ByRef DataSet As DataSet, TableName As String, SqlString As String, ParamArray Args() As Object) As DbDataAdapter
        Dim Table As New DataTable
        Dim db_da As DbDataAdapter = AddTableAdapter(Table, SqlString, Args)
        If db_da IsNot Nothing Then
            If DataSet Is Nothing Then DataSet = New DataSet
            If DataSet.Tables.Contains(TableName) Then
                DataSet.Tables(TableName).Dispose()
                DataSet.Tables.Remove(TableName)
            End If
            'Table = dt.Copy
            Table.TableName = TableName
            'dt.Dispose()
            DataSet.Tables.Add(Table)
        End If
        Return db_da
    End Function

    ''' <summary>
    ''' Creates a new table containing records from sql query and returns the dataadapter (so can update later)
    ''' </summary>
    ''' <param name="Table">New datatable to be created</param>
    ''' <param name="SqlString">Standard SQL string</param>
    ''' <param name="Args">Optional arguments when SqlString contains formatting codes</param>
    ''' <remarks>Connection is automatically opened and closed</remarks>
    Public Function AddTableAdapter(ByRef Table As DataTable, SqlString As String, ParamArray Args() As Object) As DbDataAdapter
        Try
            LastException = Nothing
            SqlString = String.Format(SqlString, Args)

            'following is global variable that traps datatable fill operations and thus allows cancellation

            'If dt IsNot Nothing Then dt.Dispose()
            'dt = Nothing
            dt = New DataTable
            ''dt.Clear()
            Dim cb As DbCommandBuilder = Nothing

            'SQLite cannot handle the extra parentheses that Access requires for join operations (but don't do this if doing sub-selects)
            Select Case dbType
                Case enumDBType.SQLite
                    If SqlString.ToUpper.Contains("JOIN") Then
                        If SqlString.Contains("FROM (") And SqlString.EndsWith(")") Then
                            SqlString = SqlString.Replace("FROM (", "FROM ")
                            SqlString = SqlString.Substring(0, SqlString.Length - 1)
                        End If
                        SqlString = SqlString.Replace("FROM (((", "FROM ")
                        SqlString = SqlString.Replace("FROM ((", "FROM ")
                        SqlString = SqlString.Replace("FROM (", "FROM ")
                        SqlString = SqlString.Replace(") LEFT", " LEFT")
                        SqlString = SqlString.Replace(") GROUP", " GROUP")
                        SqlString = SqlString.Replace(") WHERE", " WHERE")
                        SqlString = SqlString.Replace(") ORDER", " ORDER")
                    End If
            End Select

            'use global data adapter so can trap progress on fill operation

            'If db_da IsNot Nothing Then db_da.Dispose()
            'db_da = Nothing
            db_da = DataAdapter(SqlString)
            cb = CommandBuilder(db_da)

            db_da.ReturnProviderSpecificTypes = False 'causes problems if true!!!!!! 'try this to avoid returning singles from table with doubles
            'If DatabaseType = enumDBType.Oracle Then db_da.ReturnProviderSpecificTypes = True 'caused Oracle to barf
            db_da.SelectCommand.CommandTimeout = CommandTimeout

            dbCancelled = False
            db_da.Fill(dt)
            If dbCancelled Then Return Nothing

            Connection.Close()

            Table = dt.Copy
            dt.Dispose()

            Return db_da

        Catch ex As DbException
            'Sqlite will generate error when user cancels--don't want to report
            LastException = ex
            If Not ex.Message.Contains("aborted") Then Throw New DbSqlException(ex.Message, SqlString, ex)
            Return Nothing
        Catch ex As Exception
            LastException = ex
            Throw
            Return Nothing
        Finally
            RaiseProgress(1, 1, "")
        End Try
    End Function

    ''' <summary>
    ''' Create all associated commands for data adapter
    ''' </summary>
    ''' <param name="db_da">Data adapter</param>
    ''' <remarks>This was created for ARC application which is not reliably creating correct insert commands on the fly</remarks>
    Public Overridable Sub CreateAllCommands(db_da As DbDataAdapter)
    End Sub

    ''' <summary>
    ''' Creates a new data table containing records from sql query (no restriction of number of rows allowed)
    ''' </summary>
    ''' <param name="SqlString">Standard SQL string</param>
    ''' <param name="Args">Optional arguments when SqlString contains formatting codes</param>
    ''' <remarks>Connection is automatically opened and closed</remarks>
    Public Function AddTable(ByRef Table As DataTable, SqlString As String, ParamArray Args() As Object) As Boolean
        Dim dr As DbDataReader = Nothing
        Try

            LastException = Nothing
            SqlString = String.Format(SqlString, Args)

            'following is global variable that traps datatable fill operations and thus allows cancellation
            dt = New DataTable
            dt.Clear()
            Dim cb As DbCommandBuilder = Nothing

            'SQLite cannot handle the extra parentheses that Access requires for join operations (but don't do this is doing sub-selects)
            'MySQL has some bugs with MIN(Date_Time), etc. that causes result to be returned as array of bytes in some instances

            Select Case dbType
                Case enumDBType.SQLite
                    If SqlString.ToUpper.Contains("JOIN") Then
                        If SqlString.Contains("FROM (") And SqlString.EndsWith(")") Then
                            SqlString = SqlString.Replace("FROM (", "FROM ")
                            SqlString = SqlString.Substring(0, SqlString.Length - 1)
                        End If
                        SqlString = SqlString.Replace("FROM (((", "FROM ")
                        SqlString = SqlString.Replace("FROM ((", "FROM ")
                        SqlString = SqlString.Replace("FROM (", "FROM ")
                        SqlString = SqlString.Replace(") LEFT", " LEFT")
                        SqlString = SqlString.Replace(") GROUP", " GROUP")
                        SqlString = SqlString.Replace(") WHERE", " WHERE")
                        SqlString = SqlString.Replace(") ORDER", " ORDER")
                    End If
                Case enumDBType.MySQL
                    For i As Integer = 1 To 2
                        Dim ss As String = Choose(i, "MIN", "MAX") & "(DATE_TIME)"
                        Dim j As Integer = SqlString.IndexOf(ss, StringComparison.OrdinalIgnoreCase)
                        If j <> -1 Then SqlString = SqlString.Substring(0, j) & "TIMESTAMP(" & ss & ")" & SqlString.Substring(j + ss.Length)
                    Next
                    'SqlString = SqlString.ToUpper.Replace("MIN(DATE_TIME)", "TIMESTAMP(MIN(DATE_TIME))")
                    'SqlString = SqlString.ToUpper.Replace("MAX(DATE_TIME)", "TIMESTAMP(MAX(DATE_TIME))")
            End Select

            'is difficult to interrupt filling of data table; instead, create empty table and fill with data reader
            Dim s As String = NullSQLString(SqlString)

            db_da = DataAdapter(s)
            cb = CommandBuilder(db_da)

            db_da.ReturnProviderSpecificTypes = False 'causes problems if true!!!!!! 'try this to avoid returning singles from table with doubles
            db_da.SelectCommand.CommandTimeout = CommandTimeout

            db_da.Fill(dt)

            dbCancelled = False

            'following may need to commented if impacts performance too much
            'Dim numrec As Integer = ExecuteScalar("SELECT COUNT(*) FROM ({0})", SqlString)

            'time how long it is taking to fill the table; if exceeds database commandtimeout then throw error
            Dim sw As New Stopwatch
            sw.Start()

            dr = ExecuteReader(SqlString)
            If dr Is Nothing Then Return Nothing
            Dim ctr As Long = 0
            While dr.Read
                Dim r As DataRow = dt.NewRow
                For i As Integer = 0 To dt.Columns.Count - 1
                    r.Item(i) = dr.GetValue(i)
                Next
                dt.Rows.Add(r)
                If ctr Mod 1000 = 0 Then
                    'following may need to commented if impacts performance too much
                    'RaiseProgress(ctr, numrec, "Filling table...")
                    RaiseProgress(-1, 0, "Filling table...")
                    If dbCancelled Then Return False
                End If
                ctr += 1
                If sw.Elapsed.Seconds > CommandTimeout Then
                    Throw New TimeoutException(String.Format("The command timeout setting ({0} sec) has been exceeded; either narrow your query or increase the setting.", CommandTimeout))
                    Return False
                End If
            End While

            Table = dt.Copy
            Return True

            'Catch ex As DbException
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException(ex.Message, SqlString, ex)
            Return False
            'Catch ex As Exception
            '    LastException = ex
            '    Throw
            '    Return False
        Finally
            If dt IsNot Nothing Then dt.Dispose()
            If db_da IsNot Nothing Then db_da.Dispose()
            If dr IsNot Nothing Then dr.Dispose() ' dr.Close()
            CloseConnection()
            RaiseProgress(1, 1, "")
        End Try
    End Function

    ''' <summary>
    ''' Adds new table to existing dataset containing all records in table; 
    ''' uses threading so progress dialog doesn't appear to freeze during long query
    ''' </summary>
    ''' <param name="DataSet">Existing dataset</param>
    ''' <param name="TableName">Name of table to create or overwrite in dataset</param>
    ''' <remarks>Connection is automatically opened and closed</remarks>
    Public Function AddTable(ByRef DataSet As DataSet, TableName As String) As DbDataAdapter
        Return AddTable(DataSet, TableName, String.Format("Select * from {0}", TableName))
    End Function

    ''' <summary>
    ''' Perform addtable operation in a background thread; call Cancel to cancel the thread immediately (CAN'T GET THIS TO WORK--DO NOT USE!)
    ''' </summary>
    ''' <param name="DataSet">Existing or new dataset</param>
    ''' <param name="TableName">Name of table to create or overwrite in dataset</param>
    ''' <param name="SqlString">Standard sql string</param>
    ''' <remarks>Connection is automatically opened and closed</remarks>
    Public Function AddTableAsync(ByRef DataSet As DataSet, TableName As String, SqlString As String) As DbDataAdapter
        Cancelled = False
        bwAddTable = New System.ComponentModel.BackgroundWorker
        With bwAddTable
            .WorkerSupportsCancellation = True
            Dim colArg As New Collection
            colArg.Add(DataSet)
            colArg.Add(TableName)
            colArg.Add(SqlString)
            bwAddTableDataAdapter = Nothing
            .RunWorkerAsync(colArg)
            Do
                Threading.Thread.Sleep(500)
                If Cancelled Then
                    .CancelAsync()
                    .Dispose()
                    bwAddTable = Nothing
                    Cancelled = False
                    Return Nothing
                End If
            Loop Until Not IsQueryBusy
            bwAddTable.Dispose()
            bwAddTable = Nothing
            Return bwAddTableDataAdapter
        End With
    End Function

    ''' <summary>
    ''' Append instantiated database table to existing table (which must exist and be compatible)
    ''' Note: due to possibility of conflicting keys, this routine should only be called when the DestTable is empty
    ''' If you need to append records, do an export then import 
    ''' </summary>
    ''' <param name="DestTable">Name of destination table</param>
    ''' <param name="SourceTable">Name of source table</param>
    ''' <returns>True if successful</returns>
    Public Function AppendTable(DestTable As String, SourceTable As String) As Boolean
        Dim Identical As Boolean = True
        Dim lstSource As Generic.List(Of String) = GetColumnList(SourceTable)
        Dim lstDest As Generic.List(Of String) = GetColumnList(DestTable)
        If lstSource.Count <> lstDest.Count Then
            Identical = False
        Else
            For i As Integer = 0 To lstSource.Count - 1
                If lstSource(i) <> lstDest(i) Then
                    Identical = False
                    Exit For
                End If
            Next
        End If

        If Not Identical Then Throw New DbSqlException("Source and destination table structures are not identical.")
        If Not (TableExists(DestTable) And (TableExists(SourceTable) Or ViewExists(SourceTable))) Then Throw New DbSqlException("Source or destination table not found.")

        Return ExecuteNonQuery("INSERT INTO {0} SELECT * FROM {1}", FullTableName(DestTable), FullTableName(SourceTable)) <> -2
    End Function

    Private Function BytesToSingles(b() As Byte) As Single()
        Dim ar(b.Length / 4 - 1) As Single
        Buffer.BlockCopy(b, 0, ar, 0, b.Length)
        Return ar
    End Function

    '''' <summary>
    '''' Convert byte array to longs, then to hexidecimals
    '''' </summary>
    '''' <param name="b"></param>
    '''' <returns></returns>
    '''' <remarks></remarks>
    'Private Function BytesToString( b() As Byte) As String
    '    'convert byte array to longs, then to hexidecimals
    '    'each int64 is represented by 16 character hex, 8 bytes (8 bits per byte)
    '    Dim int(b.Length / 8 - 1) As Int64
    '    Buffer.BlockCopy(b, 0, int, 0, b.Length)
    '    Dim sb As New Text.StringBuilder
    '    For i As Integer = 0 To int.Length - 1
    '        sb.Append(String.Format("{0:X}", int(i)))
    '    Next
    '    Return sb.ToString
    'End Function

    ''' <summary>
    ''' Convert byte array to singles, then to semicolon-separated string
    ''' </summary>
    ''' <param name="b"></param>
    ''' <returns></returns>
    ''' <remarks>This may not be very compact, but it is human-readable</remarks>
    Private Function BytesToString(b() As Byte) As String
        Dim sng(b.Length / 4 - 1) As Single
        Buffer.BlockCopy(b, 0, sng, 0, b.Length)
        Dim sb As New Text.StringBuilder
        For i As Integer = 0 To sng.Length - 1
            If i <> 0 Then sb.Append(";")
            sb.Append(sng(i))
        Next
        Return sb.ToString
    End Function

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    ''' <remarks>
    ''' Overrides will all look like this:
    '''     Dim db As clsDB = New clsDB
    '''     MyBase.CloneSelf(db)
    '''     Return db
    ''' </remarks>
    Public MustOverride Function Clone() As clsDB

    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public MustOverride Function CommandBuilder(da As DbDataAdapter) As DbCommandBuilder

    ''' <summary>
    ''' Return provider-specific string concatenation symbol for SQL queries
    ''' </summary>
    Public Function Concat() As String
        Select Case dbType
            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Return "&"
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                Return "+"
            Case enumDBType.Oracle, enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.MySQL, enumDBType.SQLite
                Return "||"
            Case Else
                Return ""
        End Select
    End Function

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overridable Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        Throw New NotImplementedException("This data provider does not include a means to perform database creation. This function is provided by the database administrator using separate tools.")
        Return False
    End Function

    Public Enum enumCastType
        String_
        Number
        DateTime
        Integer_
        Boolean_
    End Enum

    ''' <summary>
    ''' Return proper SQL string to cast field as specified type
    ''' </summary>
    ''' <param name="FieldName">Name of field to cast</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CastAs(FieldName As String, CastType As enumCastType) As String
        Dim FieldType As String = ""
        Select Case CastType
            Case enumCastType.String_
                Select Case dbType
                    Case enumDBType.SQL_Server_CE
                        FieldType = "NVARCHAR"
                    Case enumDBType.Oracle
                        FieldType = "VARCHAR2"
                    Case enumDBType.MySQL
                        FieldType = "CHAR"
                    Case Else
                        FieldType = "VARCHAR"
                End Select
            Case enumCastType.DateTime
                Select Case dbType
                    Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.DBase
                        FieldType = "TIMESTAMP"
                    Case enumDBType.Oracle
                        FieldType = "DATE"
                    Case Else
                        FieldType = "DATETIME"
                End Select
            Case enumCastType.Number
                Select Case dbType
                    Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                        FieldType = "SINGLE"
                    Case enumDBType.Oracle
                        FieldType = "BINARY_DOUBLE"
                    Case enumDBType.MySQL
                        FieldType = "DECIMAL(12,4)"
                    Case Else
                        FieldType = "FLOAT"
                End Select
            Case enumCastType.Integer_
                Select Case dbType
                    Case enumDBType.Oracle
                        FieldType = "NUMBER(8)"
                    Case Else
                        FieldType = "INT"
                End Select
            Case enumCastType.Boolean_
                Select Case dbType
                    Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV, enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                        FieldType = "BIT"
                    Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                        FieldType = "SMALLINT"
                    Case enumDBType.Oracle
                        FieldType = "NUMBER(1)"
                    Case Else
                        FieldType = "BOOL"
                End Select
        End Select
        Return String.Format("CAST({0} AS {1})", FieldName, FieldType)
    End Function

    ''' <summary>
    ''' Return list of field names and specifications for each field in the template (e.g., TextField VARCHAR(12))
    ''' suitable for create or alter table sql statement
    ''' </summary>
    ''' <param name="dtTemplate">Data table containing table structure</param>
    Private Function GetFieldList(dtTemplate As DataTable) As Generic.List(Of String)
        Try
            LastException = Nothing

            Dim FieldList As New Generic.List(Of String)
            Dim KeyFields As New Generic.List(Of String)

            'build field list into SQL string, and use data types supported by each provider

            For Each col As DataColumn In dtTemplate.Columns
                With col
                    Dim fld As String = AddBrackets(GetValidObjectName(.ColumnName)) & " "
                    'If Me.DatabaseType = enumDBType.Access Then fld = "[" & fld.Trim & "] " 'temp for LSPC export with 'Date' field
                    If .DataType Is GetType(String) Then
                        Select Case Me.dbType
                            Case enumDBType.SQL_Server
                                fld &= "VARCHAR(" & IIf(.MaxLength = -1, 255, .MaxLength) & ")"
                            Case enumDBType.SQL_Server_CE
                                fld &= "NVARCHAR(" & IIf(.MaxLength = -1, 255, .MaxLength) & ")"
                            Case enumDBType.Oracle
                                fld &= "VARCHAR2(" & IIf(.MaxLength = -1, 255, .MaxLength) & ")"
                            Case enumDBType.DBase
                                fld &= "VARCHAR(" & IIf(.MaxLength = -1, 254, .MaxLength) & ")"
                            Case enumDBType.Access
                                fld &= IIf(.MaxLength > 250 Or .MaxLength = -1, "MEMO", "VARCHAR(" & IIf(.MaxLength = -1, 255, .MaxLength) & ")")
                            Case enumDBType.Paradox
                                fld &= IIf(.MaxLength > 3 Or .MaxLength = -1, "VAR", "") & "CHAR(" & IIf(.MaxLength = -1 Or .MaxLength > 255, 255, .MaxLength) & ")"
                            Case enumDBType.Excel
                                fld &= IIf(.MaxLength > 3 Or .MaxLength = -1, "VAR", "") & "CHAR(" & Math.Min(255, IIf(.MaxLength = -1, 255, .MaxLength)) & ")"
                            Case Else
                                fld &= IIf(.MaxLength > 3 Or .MaxLength = -1, "VAR", "") & "CHAR(" & IIf(.MaxLength = -1, 255, .MaxLength) & ")"
                        End Select
                    ElseIf .DataType Is GetType(Double) Or .DataType Is GetType(Single) Or .DataType Is GetType(Decimal) Then
                        Select Case Me.dbType
                            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                                fld &= "SINGLE"
                            Case enumDBType.Oracle
                                'fld &= "FLOAT"
                                fld &= "BINARY_DOUBLE"
                            Case enumDBType.MySQL
                                'using FLOAT for MySQL prevents proper table updates (see bug 31869);
                                'this is an exact numeric value that can range from up to almost 1 billion (for flows) and as small as 0.0001
                                fld &= "DECIMAL(12,4)"
                            Case Else
                                fld &= "FLOAT"
                        End Select
                    ElseIf .DataType Is GetType(Integer) Or .DataType Is GetType(Int64) Or .DataType Is GetType(Int16) Or .DataType Is GetType(Int32) Then
                        Select Case dbType
                            Case enumDBType.Oracle
                                fld &= "NUMBER(8)"
                            Case Else
                                fld &= "INT"
                        End Select
                    ElseIf .DataType Is GetType(Boolean) Then
                        Select Case Me.dbType
                            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV, enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                                fld &= "BIT"
                            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                                fld &= "SMALLINT"
                            Case enumDBType.Oracle
                                fld &= "NUMBER(1)"
                            Case Else
                                fld &= "BOOL"
                        End Select
                    ElseIf .DataType Is GetType(Date) Then
                        Select Case Me.dbType
                            Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.DBase
                                fld &= "TIMESTAMP"
                            Case enumDBType.Oracle
                                fld &= "DATE"
                            Case Else
                                fld &= "DATETIME"
                        End Select
                    ElseIf .DataType Is GetType(Byte()) Then 'test for blob timeseries data
                        fld &= "BLOB"
                    Else
                        Debug.Assert(False)
                    End If
                    FieldList.Add(fld)
                End With
            Next
            Return FieldList
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException("Unable to upgrade table.", ex)
        End Try
    End Function

    ''' <summary>
    ''' Upgrade existing table to use the latest schema, using structure contained in DataTable
    ''' Note: can only upgrade for new fields, not changes in field structure
    ''' Will not try to preserve field order!
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="dtTemplate">Data table containing table structure</param>
    ''' <returns>True if successful (or table doesn't exist)</returns>
    Public Function UpgradeTable(TableName As String, dtTemplate As DataTable) As Boolean

        LastException = Nothing

        'create table unless it already exists
        Dim SqlString As String = ""

        Try
            If Not TableExists(TableName) Then
                Return True
            Else
                'get field list from existing table (field names will be all caps)
                Dim TableFields As Generic.List(Of String) = GetColumnList(TableName)

                'get field list into SQL string, and use data types supported by each provider
                Dim FieldList As Generic.List(Of String) = GetFieldList(dtTemplate)

                If DatabaseType = enumDBType.Paradox Then
                    'doesn't support alter table-must export and reimport
                    Return UpgradeTableBruteForce(TableName, dtTemplate)
                Else
                    'use Alter Table command to add new fields (syntax varies slightly based on provider)
                    'note: previously tried to preserve order of columns, but that was problematic

                    'form list of new columns by removing all that already exist
                    For i As Integer = dtTemplate.Columns.Count - 1 To 0 Step -1
                        If TableFields.Contains(dtTemplate.Columns(i).ColumnName.ToUpper) Then FieldList.RemoveAt(i)
                    Next
                    For Each s As String In FieldList
                        Dim fmt As String = ""
                        Select Case DatabaseType
                            Case enumDBType.Firebird, enumDBType.Firebird, enumDBType.SQLite, enumDBType.MySQL
                                fmt = "ALTER TABLE {0} ADD COLUMN {1}"
                            Case enumDBType.Oracle
                                fmt = "ALTER TABLE {0} ADD ({1})"
                            Case Else
                                fmt = "ALTER TABLE {0} ADD {1}"
                        End Select
                        SqlString = String.Format(fmt, FullTableName(TableName), s)
                        If ExecuteNonQuery(SqlString) = -2 Then
                            Throw New DbSqlException("An error occurred while trying to upgrade the table.", SqlString)
                            Return False
                        End If
                    Next

                    'remove columns in table that are not in template
                    Dim TemplateFieldList As New Generic.List(Of String)
                    For i As Integer = 0 To dtTemplate.Columns.Count - 1
                        TemplateFieldList.Add(dtTemplate.Columns(i).ColumnName.ToUpper)
                    Next
                    For Each s As String In TableFields
                        If Not TemplateFieldList.Contains(s) Then
                            If GetIndexList(TableName).Contains(s) Or TableName = "Tracking" Then
                                UpgradeTableBruteForce(TableName, dtTemplate)
                                Continue For
                                'Throw New Exception(String.Format("Unable to upgrade table {0} because the changed field ({1}) is a primary key. This indicates a serious problem with your table which will require that you delete it and rebuild it using the Database Explorer.", TableName, s))
                            End If
                            Dim fmt As String = ""
                            Select Case DatabaseType
                                Case enumDBType.Firebird, enumDBType.Firebird, enumDBType.MySQL
                                    fmt = "ALTER TABLE {0} DROP COLUMN {1}"
                                Case enumDBType.Oracle
                                    fmt = "ALTER TABLE {0} DROP ({1})"
                                Case enumDBType.SQLite 'doesn't support DROP COLUMN
                                    Return UpgradeTableBruteForce(TableName, dtTemplate)
                                Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                                    fmt = "ALTER TABLE {0} DROP COLUMN {1}"
                                Case Else
                                    fmt = "ALTER TABLE {0} DROP {1}"
                            End Select
                            SqlString = String.Format(fmt, FullTableName(TableName), s)
                            If ExecuteNonQuery(SqlString) = -2 Then
                                Throw New DbSqlException("An error occurred while trying to upgrade the table.", SqlString)
                                Return False
                            End If
                        End If
                    Next

                    Schema_Columns = Nothing
                    Schema_Indexes = Nothing
                    Schema_IndexColumns = Nothing

                    'update the Create_Update field if applicable
                    Return UpdateRecordTimeStamp(TableName)
                End If

            End If
        Catch ex As DbSqlException
            Throw
            Return False
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException("Unable to upgrade table.", SqlString, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Upgrade existing table to use the latest schema, using structure contained in DataTable (brute force method)
    ''' Use this routine in situations where provider doesn't support appropriate Add/Drop Column syntax
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="dtTemplate">Data table containing table structure</param>
    ''' <returns>True if successful</returns>
    Public Function UpgradeTableBruteForce(TableName As String, dtTemplate As DataTable) As Boolean
        Dim TempName As String = ""
        If ExportTable(TableName, TempName) Then
            DropTable(TableName)
            If Not CreateTable(TableName, dtTemplate) Then Return False
            Return ImportTable(TableName, TempName, dtTemplate, False, False) AndAlso UpdateRecordTimeStamp(TableName)
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' If table contains CREATE_UPDATE field, set all null values to current time; if it contains OWNER field, set to current username
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="WhereClause">Additional SQL "Where" criteria to further restrict the selection ("WHERE" need not be included)</param>
    ''' <returns>True if successful (or table doesn't include field)</returns>
    ''' <remarks>Note: because of way SQLite stores dates, you must always use parameters when storing dates in tables</remarks>
    Public Function UpdateRecordTimeStamp(TableName As String, Optional WhereClause As String = "") As Boolean
        If GetColumnList(TableName).Contains("CREATE_UPDATE") And TableName.ToUpper <> "TRACKING" Then
            RaiseProgress(50, 100, "Updating all records (please stand by, this may take several minutes)...")
            If dbConnection.State <> ConnectionState.Open Then dbConnection.Open()
            Dim SqlString As String = String.Format("UPDATE {0} SET CREATE_UPDATE={1} WHERE CREATE_UPDATE IS NULL", FullTableName(TableName), ParameterName("Create_Update"))
            If WhereClause <> "" Then SqlString &= " AND " & WhereClause.ToUpper.Replace("WHERE ", "")
            Dim cmd As DbCommand = dbConnection.CreateCommand
            With cmd
                .CommandTimeout = 0
                .CommandText = SqlString
                .Parameters.Add(Parameter(cmd, "Create_Update", GetType(Date)))
                .Parameters("Create_Update").Value = Now
                .Prepare()
                .ExecuteNonQuery()
            End With
            If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
            RaiseProgress(100, 100, "")
        End If
        Return True
    End Function

    ''' <summary>
    ''' Determine if structure of table is identical to that of template (only checks field names, and doesn't check if RecID is different for Paradox tables)
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="dtTemplate">Table template</param>
    ''' <returns>True if field names exactly match</returns>
    ''' <remarks>Legacy Paradox tables don't contain RecID, Agency, or Create_Update fields; don't flag this as an error</remarks>
    Public Function CheckTableStructure(TableName As String, dtTemplate As DataTable) As Boolean

        If TableName.ToLower.Contains(".dbo.") Then Return True 'this is an external table or view---assume it is always correct

        Dim lstFieldsTable As Generic.List(Of String) = GetColumnList(TableName)
        Dim lstFieldsTemplate As New Generic.List(Of String)
        For i As Integer = 0 To dtTemplate.Columns.Count - 1
            lstFieldsTemplate.Add(GetValidObjectName(dtTemplate.Columns(i).ColumnName.ToUpper))
        Next

        For Each s As String In lstFieldsTable
            If Not lstFieldsTemplate.Contains(s) Then Return False
        Next

        For Each s As String In lstFieldsTemplate
            If (s = "RECID" Or s = "AGENCY" Or s = "CREATE_UPDATE") And dbType = enumDBType.Paradox Then Continue For 'legacy Paradox tables don't have these fields; don't flag as error
            'If s = "RECID" Then Continue For 'don't require data tables to have this field (Paradox tables are checked elsewhere)
            If s.StartsWith("OBJECT") Then Continue For 'doing testing on adding BLOBS; don't suggest upgrade
            If Not lstFieldsTable.Contains(s) Then Return False
        Next
        Return True
    End Function

    ''' <summary>
    ''' Upgrade existing table to use the latest schema, using structure contained in DataTable
    ''' Note: can only upgrade for new fields, not changes in field structure
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="dtTemplate">Table template</param>
    ''' <returns>True if successful (or table doesn't exist)</returns>
    Public Function UpgradeDataTable(TableName As String, dtTemplate As DataTable) As Boolean

        'this version will use INSERT INTO method to copy entire table assuming template has not unique primary key

        'create table unless it already exists
        Dim SqlString As String = ""
        LastException = Nothing

        Try

            If Not TableExists(TableName) Then Return True

            'rename table

            Dim TempName As String = FullTableName("TEMP_" & RemoveBrackets(TableName))
            If TableExists(TempName) Then DropTable(TempName)

            'create new empty table
            If Not CreateTable(TempName, dtTemplate) Then Return False

            'get field list from old table (field names will be all caps)
            Dim OldTableFields As Generic.List(Of String) = GetColumnList(TableName)

            'get field list from new table (field names will be all caps)
            Dim NewTableFields As Generic.List(Of String) = GetColumnList(TempName)
            'If Not IsPrimaryKeyRequired() Then NewTableFields.Remove("RECID")

            Dim FieldList As String = ""

            'find which are the new fields and substitute NULL
            For Each fld As String In NewTableFields
                If FieldList <> "" Then FieldList &= ","
                If OldTableFields.Contains(fld) Then
                    FieldList &= fld
                Else
                    FieldList &= "NULL"
                End If
            Next

            If FieldList = "" Then Return False 'if database is corrupted, table may have no fields

            SqlString = String.Format("INSERT INTO {0} ({1}) SELECT {2} FROM {3}", TempName, ArrayToCSV(NewTableFields), FieldList, FullTableName(TableName))

            Dim NumRec As Integer
            NumRec = ExecuteNonQuery(SqlString)

            If NumRec = -2 Then
                Throw New DbSqlException("An error occurred while trying to upgrade the table.", SqlString)
                Return False
            Else
                'delete the original table and rename the temp table
                DropTable(TableName)
                If Not RenameTable(TempName, TableName, dtTemplate) Then Return False
                UpdateRecordTimeStamp(TableName)
                Schema_Columns = Nothing
                Schema_Indexes = Nothing
                Schema_IndexColumns = Nothing
                Return True
            End If

        Catch ex As DbSqlException
            Throw
            Return False
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException("Unable to upgrade table.", SqlString, ex)
            Return False
        End Try
    End Function

    Public Function CreateNullViewSQL(TableName As String, dtTemplate As DataTable) As String
        Dim SqlString As String = $"CREATE VIEW [{TableName}] AS SELECT "
        For Each col As DataColumn In dtTemplate.Columns
            SqlString &= $"NULL AS [{col.ColumnName}], "
        Next
        SqlString = SqlString.Substring(0, SqlString.Length - 2) & " WHERE 1=2" 'strip off final comma and add condition so always returns empty view
        Return SqlString
    End Function

    Public Function CreateTableSQL(TableName As String, dtTemplate As DataTable, ByRef Optional SqlStringIndex As String = "") As String
        Dim SqlString As String = ""

        'build field list into SQL string, and use data types supported by each provider
        Dim FieldList As Generic.List(Of String) = GetFieldList(dtTemplate)

        Dim KeyFields As New List(Of String)

        'most providers require that primary keys be designated NOT NULL, but some must not
        For i As Integer = 0 To dtTemplate.Columns.Count - 1
            Dim col As DataColumn = dtTemplate.Columns(i)
            With col
                If IsPrimaryKey(dtTemplate, col) Then
                    Select Case dbType
                        Case enumDBType.CSV, enumDBType.DBase, enumDBType.Excel, enumDBType.Paradox, enumDBType.Text
                        Case Else
                            FieldList(i) &= " NOT NULL"
                    End Select
                    KeyFields.Add(GetValidObjectName(.ColumnName))
                End If
            End With
        Next

        If KeyFields.Count > 0 Then
            Select Case dbType
                Case enumDBType.Paradox
                    SqlString = String.Format("CREATE TABLE {0} ({1})", FullTableName(TableName), ArrayToCSV(FieldList.ToArray))
                    SqlStringIndex = String.Format("CREATE UNIQUE INDEX {0} ON {0} ({1}) WITH PRIMARY", FullTableName(TableName), ArrayToCSV(KeyFields.ToArray))
                Case enumDBType.DBase, enumDBType.Excel, enumDBType.CSV, enumDBType.Text 'these don't support primary keys
                    SqlString = String.Format("CREATE TABLE {0} ({1})", FullTableName(TableName), ArrayToCSV(FieldList.ToArray))
                Case Else
                    SqlString = String.Format("CREATE TABLE {0} ({1}, PRIMARY KEY ({2}))", FullTableName(TableName), ArrayToCSV(FieldList), ArrayToCSV(KeyFields))
            End Select
        Else
            SqlString = String.Format("CREATE TABLE {0} ({1})", FullTableName(TableName), ArrayToCSV(FieldList))
        End If

        Return SqlString
    End Function

    ''' <summary>
    ''' Create table if it doesn't already exist, using structure contained in DataTable
    ''' </summary>
    ''' <param name="TableName">Desired name of table</param>
    ''' <param name="dtTemplate">Data table containing table structure</param>
    ''' <returns>True if successful</returns>
    Public Function CreateTable(ByRef TableName As String, dtTemplate As DataTable) As Boolean

        Dim SqlStringIndex As String = ""
        Dim SqlString As String = CreateTableSQL(TableName, dtTemplate, SqlStringIndex)
        LastException = Nothing

        Try
            If TableExists(FullTableName(TableName)) Then
                ExecuteNonQuery("DELETE FROM " & FullTableName(TableName))
                Return True
            Else
                DropView(FullTableName(TableName))

                Dim NumRec As Integer
                If SqlString <> "" Then
                    NumRec = ExecuteNonQuery(SqlString)
                    If dbType = enumDBType.Paradox AndAlso NumRec <> -2 Then
                        'paradox driver has problem if specify primary key in create table statement; must do two queries
                        NumRec = ExecuteNonQuery(SqlStringIndex)
                    End If
                Else
                    NumRec = ExecuteNonQuery(SqlString)
                End If

                Schema_Tables = Nothing
                Schema_Columns = Nothing
                Schema_Indexes = Nothing
                Schema_IndexColumns = Nothing

                If NumRec = -2 Then
                    Throw New DbSqlException("An error occurred while trying to create the table.", SqlString)
                    Return False
                Else
                    Return True
                End If
            End If
        Catch ex As DbSqlException
            Throw
            Return False
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException("Unable to create table.", SqlString, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Create table if it doesn't already exist, using structure defined in arguments (as when tables need to be created on the fly for reports)
    ''' </summary>
    ''' <param name="TableName">Desired name of table</param>
    ''' <param name="ColumnNames">Array of column names</param>
    ''' <param name="ColumnTypes">Array of column types</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Note: string columns are always given a length of 255 characters</remarks>
    Public Function CreateTable(ByRef TableName As String, ColumnNames() As String, ColumnTypes() As enumColType) As Boolean

        'create table unless it already exists
        Dim SqlString As String = ""
        LastException = Nothing

        Try
            If TableExists(TableName) Then
                Return True
            Else
                'build field list into SQL string, and use data types supported by each provider
                Dim FieldList As New Generic.List(Of String)

                For i As Integer = 0 To ColumnNames.Length - 1
                    Dim fld As String = GetValidObjectName(ColumnNames(i)) & " "
                    If ColumnTypes(i) = enumColType.String Then
                        Select Case Me.dbType
                            Case enumDBType.DBase
                                fld &= "VARCHAR(254)"
                            Case enumDBType.SQL_Server_CE
                                fld &= "NVARCHAR(255)"
                            Case Else
                                fld &= "VARCHAR(255)"
                        End Select
                    ElseIf ColumnTypes(i) = enumColType.Double Then
                        Select Case Me.dbType
                            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                                fld &= "SINGLE"
                            Case enumDBType.Oracle
                                'fld &= "FLOAT"
                                fld &= "BINARY_DOUBLE"
                            Case enumDBType.MySQL
                                'using FLOAT for MySQL prevents proper table updates (see bug 31869);
                                'this is an exact numeric value that can range from up to almost 1 billion (for flows) and as small as 0.0001
                                fld &= "DECIMAL(12,4)"
                            Case Else
                                fld &= "FLOAT"
                        End Select
                    ElseIf ColumnTypes(i) = enumColType.Integer Then
                        Select Case dbType
                            Case enumDBType.Oracle
                                fld &= "NUMBER(8)"
                            Case Else
                                fld &= "INT"
                        End Select
                    ElseIf ColumnTypes(i) = enumColType.Boolean Then
                        Select Case Me.dbType
                            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV, enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                                fld &= "BIT"
                            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                                fld &= "SMALLINT"
                            Case enumDBType.Oracle
                                fld &= "NUMBER(1)"
                            Case Else
                                fld &= "BOOL"
                        End Select
                    ElseIf ColumnTypes(i) = enumColType.Date Then
                        Select Case Me.dbType
                            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                                fld &= "TIMESTAMP"
                            Case enumDBType.Oracle
                                fld &= "DATE"
                            Case Else
                                fld &= "DATETIME"
                        End Select
                    End If
                    FieldList.Add(fld)
                Next
                Dim NumRec As Integer
                SqlString = String.Format("CREATE TABLE {0} ({1})", FullTableName(TableName), ArrayToCSV(FieldList))
                NumRec = ExecuteNonQuery(SqlString)

                Schema_Tables = Nothing
                Schema_Columns = Nothing
                Schema_Indexes = Nothing
                Schema_IndexColumns = Nothing

                If NumRec = -2 Then
                    Throw New DbSqlException("An error occurred while trying to create the table.", SqlString)
                    Return False
                ElseIf dbType = enumDBType.Paradox And ColumnNames(0).ToUpper = "RECID" Then
                    SqlString = String.Format("CREATE UNIQUE INDEX {0} ON {0} ({1}) WITH PRIMARY", FullTableName(TableName), "RECID")
                    NumRec = ExecuteNonQuery(SqlString)
                    If NumRec = -2 Then
                        Throw New DbSqlException("An error occurred while trying to create the primary index.", SqlString)
                        Return False
                    Else
                        Return True
                    End If
                    Return True
                Else
                    Return True
                End If
            End If
        Catch ex As Exception
            LastException = ex
            Throw
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Create datatable using structure defined in arguments (as when tables need to be created on the fly for reports) and fill with data identified in SQL string
    ''' </summary>
    ''' <param name="DT">Datatable to create and store data in</param>
    ''' <param name="ColumnNames">Array of column names</param>
    ''' <param name="ColumnTypes">Array of column types</param>
    ''' <param name="SqlString">Query string as source of data</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Will load all results from query into in-memory table which will take longer for some reports</remarks>
    Public Function CreateTable(ByRef DT As DataTable, ColumnNames() As String, ColumnTypes() As enumColType, SqlString As String) As Boolean
        Dim dr As DbDataReader = Nothing
        LastException = Nothing
        Try
            With DT
                For i As Integer = 0 To ColumnNames.Length - 1
                    .Columns.Add(ColumnNames(i), Choose(ColumnTypes(i) + 1, GetType(String), GetType(Double), GetType(Integer), GetType(Boolean), GetType(Date)))
                Next
                dr = ExecuteReader(SqlString)
                While dr IsNot Nothing And dr.Read
                    Dim values(ColumnNames.Length - 1) As Object
                    dr.GetValues(values)
                    .Rows.Add(values)
                End While
            End With
            Return True
        Catch ex As Exception
            LastException = ex
            Throw
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
        End Try
    End Function

    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public MustOverride Function DataAdapter(SqlString As String) As DbDataAdapter

    ''' <summary>
    ''' Create comma-separated value list from set of parameters
    ''' </summary>
    ''' <param name="ObjList">String parameters</param>
    ''' <returns>CSV list suitable for Insert or Update SQL statement</returns>
    Public Function ValueList(ParamArray ObjList() As Object) As String
        Dim s As String = ""
        For Each o As Object In ObjList
            If s <> "" Then s &= ","
            If TypeOf o Is String Then
                s &= TextString(o)
            ElseIf TypeOf o Is Boolean Then
                s &= Logical(o)
            ElseIf TypeOf o Is Date Then
                s &= DateTime(o)
            Else
                s &= Numeric(o)
            End If
        Next
        Return s
    End Function

    ''' <summary>
    ''' Return provider-specific date-time string for SQL queries
    ''' </summary>
    ''' <param name="DateTimeValue">Date-time to convert (may pass null value)</param>
    ''' <remarks>Because of way SQLite stores dates, should be careful using this function to store date values in table (parameters are better)</remarks>
    Public Function DateTime(DateTimeValue As Object) As String
        If DateTimeValue Is Nothing OrElse IsDBNull(DateTimeValue) OrElse DateTimeValue.ToString = "" OrElse Not IsDate(DateTimeValue) OrElse CDate(DateTimeValue).Year < 1800 Then Return "NULL"
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Return String.Format("#{0:MM/dd/yyyy HH:mm:ss}#", CDate(DateTimeValue))
            Case enumDBType.SQLite
                'Return String.Format("'{0:yyyy-MM-dd HH:mm:ss.0000000}'", CDate(DateTimeValue))
                Return String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", CDate(DateTimeValue))
            Case enumDBType.Oracle
                Return String.Format("TIMESTAMP '{0:yyyy-MM-dd HH:mm:ss}'", CDate(DateTimeValue))
            Case enumDBType.MySQL
                Return String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", CDate(DateTimeValue))
            Case Else
                Return String.Format("'{0:MM/dd/yyyy HH:mm:ss}'", CDate(DateTimeValue))
        End Select
    End Function

    ''' <summary>
    ''' Return provider-specific text string for SQL queries
    ''' </summary>
    ''' <param name="Text">Text string to convert</param>
    Public Function TextString(Text As Object) As String
        If Text Is Nothing OrElse IsDBNull(Text) OrElse (TypeOf Text Is String AndAlso String.IsNullOrEmpty(Text)) Then Return "NULL"
        Return String.Format("'{0}'", CStr(Text).Replace("'", "''")) 'tick marks are escaped by doubling them
    End Function

    ''' <summary>
    ''' Return provider-specific text string for SQL queries; limit the length
    ''' </summary>
    ''' <param name="Text">Text string to convert</param>
    Public Function TextString(Text As Object, MaxLength As Integer) As String
        If Text Is Nothing OrElse IsDBNull(Text) OrElse (TypeOf Text Is String AndAlso String.IsNullOrEmpty(Text)) Then Return "NULL"
        Return String.Format("'{0}'", CStr(Strings.Left(Text, MaxLength)).Replace("'", "''")) 'tick marks are escaped by doubling them
    End Function

    ''' <summary>
    ''' Return provider-specific numeric string for SQL queries
    ''' </summary>
    ''' <param name="Value">Numeric value to convert</param>
    Public Function Numeric(Value As Object) As String
        If Value Is Nothing OrElse IsDBNull(Value) OrElse CSng(Value) <= Single.MinValue OrElse Double.IsNaN(Value) Then Return "NULL"
        Return CDbl(Value).ToString(Globalization.CultureInfo.InvariantCulture)
    End Function

    ''' <summary>
    ''' Provider handle logicals differently; this will return proper way to represent true/false in SQL queries
    ''' </summary>
    ''' <param name="True_False">True or False</param>
    Public Function Logical(True_False As Object) As String
        If True_False Is Nothing OrElse IsDBNull(True_False) Then Return "NULL"
        Dim b As Boolean = False
        Boolean.TryParse(True_False, b)
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV, enumDBType.SQL_Server, enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.SQLite, enumDBType.MySQL, enumDBType.Oracle, enumDBType.SQL_Server_CE
                Return IIf(b, "1", "0")
            Case enumDBType.Access
                Return b.ToString
            Case Else
                Debug.Assert(False)
                Return IIf(b, "'True'", "'False'")
        End Select
    End Function

    ''' <summary>
    ''' Return provider-specific SQL syntax to convert date-time to MM/DD/YYYY date format
    ''' </summary>
    ''' <param name="DateTimeFieldName">Name of field to convert</param>
    Public Function DateFormat(DateTimeFieldName As String) As String
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Return String.Format("FORMAT({0},'MM/DD/YYYY')", DateTimeFieldName)
            Case enumDBType.SQLite
                Return String.Format("strftime('%m/%d/%Y',{0})", DateTimeFieldName)
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                Return String.Format("CAST(EXTRACT(MONTH FROM {0}) AS VARCHAR(2)) || '/' || CAST(EXTRACT(DAY FROM {0}) AS VARCHAR(2)) || '/' || CAST(EXTRACT(YEAR FROM {0}) AS VARCHAR(4))", DateTimeFieldName)
            Case enumDBType.Oracle
                Return String.Format("TO_CHAR({0},'MM/DD/YYYY')", DateTimeFieldName)
            Case enumDBType.MySQL
                Return String.Format("DATE_FORMAT({0},'%m/%d/%Y')", DateTimeFieldName)
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                Return String.Format("CONVERT(VARCHAR(10), {0}, 101)", DateTimeFieldName)
            Case Else
                Return DateTimeFieldName
        End Select
    End Function

    Public Enum enumDateFormat
        MMDDYYYY
        MM01YYYY
        YYYYMM
    End Enum

    ''' <summary>
    ''' Return provider-specific SQL syntax to convert date-time to desired date format; this is used by LSPC cross tab to consolidate data by month
    ''' </summary>
    ''' <param name="DateTimeFieldName">Name of field to convert</param>
    Public Function DateFormat(DateTimeFieldName As String, Format As enumDateFormat) As String
        Dim Fmt As String = ""
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "MM/DD/YYYY"
                    Case enumDateFormat.MM01YYYY : Fmt = "MM/01/YYYY"
                    Case enumDateFormat.YYYYMM : Fmt = "YYYYMM"
                End Select
                Return String.Format("FORMAT({0},'{1}')", DateTimeFieldName, Fmt)
            Case enumDBType.SQLite
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "%m/%d/%Y"
                    Case enumDateFormat.MM01YYYY : Fmt = "%m/01/%Y"
                    Case enumDateFormat.YYYYMM : Fmt = "%Y%m"
                End Select
                Return String.Format("strftime('{1}',{0})", DateTimeFieldName, Fmt)
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "CAST(EXTRACT(MONTH FROM {0}) AS VARCHAR(2)) || '/' || CAST(EXTRACT(DAY FROM {0}) AS VARCHAR(2)) || '/' || CAST(EXTRACT(YEAR FROM {0}) AS VARCHAR(4))"
                    Case enumDateFormat.MM01YYYY : Fmt = "CAST(EXTRACT(MONTH FROM {0}) AS VARCHAR(2)) || '/01/' || CAST(EXTRACT(YEAR FROM {0}) AS VARCHAR(4))"
                    Case enumDateFormat.YYYYMM : Fmt = "CAST(EXTRACT(YEAR FROM {0}) AS VARCHAR(4)) || CAST(EXTRACT(MONTH FROM {0}) AS VARCHAR(2))"
                End Select
                Return String.Format(Fmt, DateTimeFieldName)
            Case enumDBType.Oracle
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "MM/DD/YYYY"
                    Case enumDateFormat.MM01YYYY : Fmt = "MM/01/YYYY"
                    Case enumDateFormat.YYYYMM : Fmt = "YYYYMM"
                End Select
                Return String.Format("TO_CHAR({0},'{1}')", DateTimeFieldName, Fmt)
            Case enumDBType.MySQL
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "%m/%d/%Y"
                    Case enumDateFormat.MM01YYYY : Fmt = "%m/01/%Y"
                    Case enumDateFormat.YYYYMM : Fmt = "%Y%m"
                End Select
                Return String.Format("DATE_FORMAT({0},'{1}')", DateTimeFieldName, Fmt)
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                'not sure these will work!!!!
                Dim Len As Integer, Num As Integer
                Select Case Format
                    Case enumDateFormat.MMDDYYYY : Fmt = "MM/DD/YYYY" : Num = 101
                    Case enumDateFormat.MM01YYYY : Fmt = "MM/01/YYYY" : Num = 101
                    Case enumDateFormat.YYYYMM : Fmt = "YYYYMM" : Num = 112
                End Select
                Return String.Format("CONVERT(VARCHAR({1}), {0}, {3}) AS [{2}]", DateTimeFieldName, Len, Fmt, Num)
            Case Else
                Return DateTimeFieldName
        End Select
    End Function

    ''' <summary>
    ''' Return provider-specific SQL syntax to convert numeric to string format
    ''' </summary>
    ''' <param name="NumericFieldName">Name of field to convert</param>
    ''' <param name="NumDecimals">Number of decimal places for format</param>
    Public Function NumberFormat(NumericFieldName As String, NumDecimals As Integer) As String
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Return String.Format("FORMAT({0},'#.{1}')", NumericFieldName, "0".PadRight(NumDecimals, "0"))
            Case enumDBType.SQLite
                Return String.Format("ROUND({0},2)", NumericFieldName) 'this is not quite right, could get 27.1 instead of 27.10 (no trailing zeros)
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                Return String.Format("CAST(CAST({0} AS DECIMAL(20,{1})) AS VARCHAR(20))", NumericFieldName, NumDecimals)
            Case enumDBType.Oracle
                Return String.Format("TRIM(TO_CHAR({0},'9999999999.{1}')", NumericFieldName, "9".PadRight(NumDecimals, "9"))
            Case enumDBType.MySQL
                Return String.Format("CAST(CAST({0} AS DECIMAL(20,{1})) AS CHAR)", NumericFieldName, NumDecimals)
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                Return String.Format("CAST(CAST({0} AS DECIMAL(20,{1})) AS VARCHAR)", NumericFieldName, NumDecimals)
            Case Else
                Return NumericFieldName
        End Select
    End Function

    ''' <summary>
    ''' Create tab-delimited text file and export table to it
    ''' </summary>
    ''' <param name="TableName">Name of table to export</param>
    ''' <param name="ExportFileName">Name of file to export to (if blank, will export to temp file and return name here; caller must place in clipboard and delete tempfile)</param>
    ''' <param name="SQLString">If provided, uses SQL statement to select data to be exported</param>
    ''' <returns>True if export successful</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overridable Function ExportTable(TableName As String, ByRef ExportFileName As String, Optional SQLString As String = "", Optional UseCurrentCulture As Boolean = False) As Boolean

        If Not (TableExists(TableName) Or ViewExists(TableName)) Then Return False

        Dim sw As System.IO.StreamWriter = Nothing
        Dim dr As DbDataReader = Nothing
        Try
            Cancelled = False

            If Not OpenConnection() Then Return False

            RaiseProgress(0, 100, String.Format("Exporting {0}...", Proper(TableName)))

            Dim NumRecords As Long

            Dim SQL As String = SQLString
            If SQL = "" Then
                NumRecords = Me.GetRecordCount(TableName)
                SQL = String.Format("SELECT * FROM {0}", FullTableName(TableName))
            Else
                NumRecords = -1 'Me.ExecuteScalar("SELECT COUNT(*) FROM ({0}) AS TBL", SQL)
            End If

            dr = Me.ExecuteReader(SQL)
            If dr Is Nothing Then Return False

            Dim ar(dr.FieldCount - 1) As Object

            If ExportFileName = "" Then ExportFileName = My.Computer.FileSystem.GetTempFileName

            sw = New System.IO.StreamWriter(ExportFileName, False)
            Dim RecNum As Long = 0

            Dim dt As DataTable = dr.GetSchemaTable()
            Dim fldName(dt.Rows.Count - 1) As String
            Dim fldType(dt.Rows.Count - 1) As String
            Dim fldSize(dt.Rows.Count - 1) As String
            Dim sqlType(dt.Rows.Count - 1) As String

            Dim ObjectTypeColNum As Integer = -1
            Dim FirstColWritten As Boolean = False

            'write column names on the first row
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim ColName As String = dt.Rows(i)("ColumnName")
                If ColName.ToUpper <> "RECID" Then
                    sw.Write(IIf(FirstColWritten, vbTab, "") & ColName)
                    FirstColWritten = True
                End If
                If String.Equals(ColName, "Object_Type", StringComparison.OrdinalIgnoreCase) Then ObjectTypeColNum = i

            Next
            sw.WriteLine()
            Dim LastTime As Date = Now

            While dr.Read()
                If RecNum = 0 OrElse Now.Subtract(LastTime).TotalSeconds > 1 Then
                    RaiseProgress(RecNum, NumRecords, String.Format("Extracting record {0:N0}{1}{2}", RecNum, IIf(NumRecords = -1, "", " of " & NumRecords.ToString("N0")), IIf(TableName = "", "", " from " & TableName)))
                    If Cancelled Then
                        sw.Close()
                        dr.Close()
                        dr.Dispose()
                        Return False
                    End If
                    LastTime = Now
                End If
                RecNum += 1

                If ar Is Nothing Then ReDim ar(dr.FieldCount - 1)

                'must wrap in try/catch because Sqlite may throw an error if invalid data is encountered
                Try
                    dr.GetValues(ar)
                Catch ex As Exception
                    Debug.Print("Error encountered at " & RecNum)
                    For i As Integer = 0 To dt.Rows.Count - 1
                        Try
                            ar(i) = dr.GetValue(i)
                        Catch ex2 As Exception
                            ar(i) = DBNull.Value
                        End Try
                    Next
                End Try

                Dim BaseDate As Date = Date.MinValue
                FirstColWritten = False

                'note: writing individual fields this way (using StreamWriter) is not real efficient, however it accounts for 0% of the time of this routine (database access is 100%)

                For i As Int16 = 0 To ar.Length - 1
                    If dt.Rows(i)("ColumnName").ToUpper = "RECID" Then Continue For
                    If FirstColWritten Then sw.Write(vbTab)
                    If TypeOf ar(i) Is Boolean Then 'don't want to write "TRUE", write 1 or 0
                        sw.Write(IIf(ar(i), 1, 0))
                    ElseIf TypeOf ar(i) Is Date Then
                        sw.Write(String.Format("{0:yyyy-MM-dd HH:mm}", ar(i)))
                        If dt.Rows(i)("ColumnName") = "Date_Time" Then BaseDate = ar(i)
                    ElseIf TypeOf ar(i) Is String Then
                        Dim s As String = DirectCast(TestNull(ar(i), ""), String).Replace(vbTab, "").Replace(vbCr, "").Replace(vbLf, "") 'add the following to detect if string field contains quote; if so, convert to double quotes and surround field in quotes
                        If s.Contains("""") Then s = """" & s.Replace("""", """""") & """"
                        sw.Write(s)
                    ElseIf TypeOf ar(i) Is Byte() Then  'test for blob timeseries data
                        If ObjectTypeColNum <> -1 Then
                            'treat contents of column as file extension
                            Dim ext As String = TestNull(ar(ObjectTypeColNum), "")

                            'save as separate file tagged with record number
                            Dim BlobFile As String = String.Format("{0}\{1}{2}.{3}", IO.Path.GetDirectoryName(ExportFileName), IO.Path.GetFileNameWithoutExtension(ExportFileName), RecNum - 1, ext.ToLower)
                            clsBlob.SaveBlob(ext, BlobFile, ar(i), BaseDate)
                        End If
                    Else 'must be a number
                        If IsDBNull(ar(i)) Then
                            sw.Write("")
                        Else
                            If UseCurrentCulture Then
                                sw.Write(Math.Round(Val(ar(i)), 6).ToString(Globalization.CultureInfo.CurrentCulture)) 'round to single-precision
                            Else
                                sw.Write(Math.Round(Val(ar(i)), 6).ToString(Globalization.CultureInfo.InvariantCulture)) 'round to single-precision
                            End If
                        End If
                    End If
                    FirstColWritten = True
                Next
                sw.WriteLine()
                If Cancelled Then Return False
            End While

            Return True
        Catch ex As Exception
            Throw
        Finally
            If sw IsNot Nothing Then sw.Close() : sw.Dispose()
            If dr IsNot Nothing Then dr.Close() : dr.Dispose()
            CloseConnection()
            RaiseEvent Progress(Me, 100, 100, "")
        End Try
    End Function

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' Return tablename enclosed in brackets (if applicable) suitable for SQL statements; if tablename is subselect, no changes are made
    ''' </summary>
    ''' <param name="TblName">Name of table</param>
    Public Overridable Function FullTableName(TblName As String) As String
        If TblName.StartsWith("(") Then Return TblName
        Select Case dbType
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.MySQL ', enumDBType.Oracle
                Return TblName
            Case enumDBType.Oracle
                Return AddBrackets(RemoveBrackets(DatabaseName & "." & TableName(TblName)))
            'Case enumDBType.Excel
            '    Return "[" & TableName.Replace("[", "").Replace("]", "").Replace("$", "") & "]"
            'Case enumDBType.MySQL
            '    Return "`" & TableName.Replace("`", "") & "`"
            Case enumDBType.Paradox
                Return "[" & RemoveBrackets(TblName.ToUpper.Replace(".DB", "")) & "]"
            Case Else
                If TblName.ToLower.Contains(".dbo.") Then Return TblName 'reference to external table; leave it alone
                Return "[" & RemoveBrackets(TblName) & "]"
        End Select
    End Function

    ''' <summary>
    ''' Return standard table name without brackets, etc.
    ''' </summary>
    ''' <param name="FullTableName">Full table name with brackets, etc.</param>
    Public Function TableName(FullTableName As String) As String
        Dim tbl As String = RemoveBrackets(FullTableName)
        If Not tbl.StartsWith("(") AndAlso tbl.Contains(".") Then 'Oracle has database prefix
            tbl = tbl.Replace(".DBO", "").Split(".")(1) 'when open alternate table also has DBO for sql server
        End If
        Return tbl
    End Function

    ''' <summary>
    ''' Return a list of columns for this table in the order they appear in the table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param> 
    ''' <returns></returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overridable Function GetColumnList(TableName As String) As Generic.List(Of String)
        Try
            LastException = Nothing
            TableName = RemoveBrackets(TableName)

            'note: sometimes the drive prepends _ and othertimes not...handle below

            'If dbType = enumDBType.Excel Then TableName = "_" & TableName

            If Schema_Columns Is Nothing AndAlso Not OpenConnection() Then Return Nothing

            Try
                If dbType = enumDBType.Text Or dbType = enumDBType.CSV Or dbType = enumDBType.DBase Then 'only look at specific table, or empty or malformed files will cause error
                    OpenConnection()
                    Schema_Columns = dbConnection.GetSchema("Columns", New String() {Nothing, Nothing, TableName})
                ElseIf dbDatabaseName = "" OrElse dbType = enumDBType.MySQL OrElse dbType = enumDBType.Oracle Then
                    If Schema_Columns Is Nothing Then Schema_Columns = dbConnection.GetSchema("Columns")
                Else
                    'restrictions argument are Catalog (database), Schema, Tablename, Tabletype
                    'note: there is a bug in some versions of SQL server that will not return column names containing periods
                    If Schema_Columns Is Nothing Then Schema_Columns = dbConnection.GetSchema("Columns", New String() {Me.dbDatabaseName})
                End If
            Catch ex As Exception
                Throw New ApplicationException("A severe error has occurred while accessing the database schema, indicating that the database has become corrupted. It is recommended that you either restore the database from a recent computer backup or create a new database and restore backup data from your most recent archive.")
            End Try

            Dim dictColumns As New Generic.SortedDictionary(Of Integer, String)
            For Each row As DataRow In Schema_Columns.Rows
                Dim TblName As String = row.Item("Table_Name")
                Dim ColName As String = row.Item("Column_Name")
                Dim Ordinal As Integer = row.Item("Ordinal_Position")
                If TblName.Equals(TableName, StringComparison.OrdinalIgnoreCase) OrElse (dbType = enumDBType.Excel And TblName.Equals("_" & TableName, StringComparison.OrdinalIgnoreCase)) Then
                    If Not dictColumns.ContainsKey(Ordinal) Then dictColumns.Add(Ordinal, ColName.ToUpper)
                End If
            Next
            Dim lstColumns As New Generic.List(Of String)
            For Each kv As Generic.KeyValuePair(Of Integer, String) In dictColumns
                lstColumns.Add(GetValidObjectName(kv.Value))
            Next
            Return lstColumns

        Catch ex As Exception
            LastException = ex
            Return New Generic.List(Of String) 'return empty list to avoid later errors
        End Try
    End Function

    ''' <summary>
    ''' Return list of indexed columns for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>List of indexed fields (not including RecID) or Nothing if error occurred</returns>
    Public Overridable Function GetIndexList(TableName As String) As Generic.List(Of String)
        Try
            LastException = Nothing
            If (Schema_IndexColumns Is Nothing OrElse Schema_Indexes Is Nothing) AndAlso Not OpenConnection() Then Return Nothing

            Dim lstIndex As New Generic.List(Of String)

            Select Case dbType
                Case enumDBType.MySQL, enumDBType.Oracle, enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.SQL_Server, enumDBType.SQLite, enumDBType.SQL_Server_CE
                    If Schema_IndexColumns Is Nothing Then Schema_IndexColumns = dbConnection.GetSchema("IndexColumns")
                    For Each row As DataRow In Schema_IndexColumns.Rows
                        Dim TblName As String = row.Item("Table_Name")
                        Dim ColName As String = row.Item("Column_Name")
                        If TblName.Equals(TableName, StringComparison.OrdinalIgnoreCase) Then lstIndex.Add(ColName.ToUpper)
                    Next
            End Select

            'some providers don't place primary key indices in IndexColumns schema table, but instead just have an entry in the Indexes schema table
            'if so, add another entry

            If dbDatabaseName <> "" Then
                If Schema_Indexes Is Nothing Then Schema_Indexes = dbConnection.GetSchema("Indexes", New String() {Me.dbDatabaseName})
            Else
                If Schema_Indexes Is Nothing Then Schema_Indexes = dbConnection.GetSchema("Indexes")
            End If
            For Each row As DataRow In Schema_Indexes.Rows
                Dim TblName As String = row.Item("Table_Name")
                Dim ColName As String = "PrimaryKey"
                If Schema_Indexes.Columns.Contains("Column_Name") AndAlso Not IsDBNull(row.Item("Column_Name")) Then ColName = row.Item("Column_Name")
                If TblName.Equals(TableName, StringComparison.OrdinalIgnoreCase) AndAlso lstIndex.Count = 0 Then lstIndex.Add(ColName.ToUpper)
            Next

            Return lstIndex
        Catch ex As Exception
            LastException = ex
            Throw
            Return Nothing
        Finally
            If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Determine the number of records in a table by starting a new thread (using default timeout of 10 sec)
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Number of records (if timeout occurs, will return -1)</returns>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Overridable Function GetRecordCount(TableName As String) As Integer
        Return GetRecordCount(TableName, 10)
    End Function

    ''' <summary>
    ''' Determine the number of records in a table by starting a new thread
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Number of records (if timeout occurs, will return -1)</returns>
    ''' <param name="CommandTimeout">Number of seconds before timeout exception occurs (may be ignored by MySQL)</param>
    <DebuggerStepThrough()>
    Public Overridable Function GetRecordCount(TableName As String, CommandTimeout As Integer) As Integer
        If dbConnection Is Nothing Then Return 0
        If Not (TableExists(TableName) OrElse ViewExists(TableName)) Then Return 0
        If Not OpenConnection() Then Return 0
        Dim cmd As DbCommand = dbConnection.CreateCommand
        cmd.CommandText = String.Format("SELECT COUNT(*) FROM {0} Dta", FullTableName(TableName))
        If dbType = enumDBType.SQL_Server_CE Then CommandTimeout = 0
        cmd.CommandTimeout = CommandTimeout
        Try
            Return cmd.ExecuteScalar
        Catch ex As DbException
            Return -1
        Finally
            cmd.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' Return a sorted list of unique values from a specified field in a table. This is useful for getting key field values
    ''' </summary>
    ''' <param name="TableName">Table name to query</param>
    ''' <param name="ColumnName">Column (field) to return</param>
    ''' <param name="WhereClause">Additional SQL "Where" criteria to further restrict the selection (must include "WHERE")</param>
    Public Overridable Function GetRecordList(TableName As String, ColumnName As String, Optional WhereClause As String = "") As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        LastException = Nothing
        Try
            dr = ExecuteReader("SELECT DISTINCT {0} FROM {1} Dta {2} ORDER BY {0}", ColumnName, FullTableName(TableName), WhereClause)
            Dim lst As New Generic.List(Of String)
            While dr IsNot Nothing AndAlso dr.Read
                If Not dr.IsDBNull(0) Then lst.Add(dr.GetValue(0).ToString)
            End While
            Return lst
        Catch ex As Exception
            LastException = ex
            Throw
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of reserved words for this provider
    ''' </summary>
    Public Overridable Function GetReservedWords() As Generic.List(Of String)
        Static Dim col As Generic.List(Of String) = Nothing
        LastException = Nothing
        If col Is Nothing Then
            col = New Generic.List(Of String)
            Try
                Dim SchemaTable As DataTable = Nothing
                If Not OpenConnection() Then Return Nothing
                SchemaTable = dbConnection.GetSchema("ReservedWords")
                For Each row As DataRow In SchemaTable.Rows
                    Dim colName As String = "ReservedWord"
                    If dbType = enumDBType.MySQL Then colName = "Reserved Word" 'they spelled it wrong!
                    col.Add(row.Item(colName))
                Next
            Catch ex As Exception
                LastException = ex
                Throw
                'Return col 'may be empty collection
            Finally
                'If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
            End Try
        End If
        Return col
    End Function

    ''' <summary>
    ''' Return short name for path (without spaces, and does not require quotes on command line)
    ''' </summary>
    Public Function GetShortName(path As String) As String
        Dim sb As New System.Text.StringBuilder(259)
        Dim retval As Integer = GetShortPathName(path, sb, 259)
        If retval = 0 Then Throw New IO.IOException("Could not retrieve short name", retval)
        Return sb.ToString()
    End Function

    ''' <summary>
    ''' Return list of databases available on the server
    ''' </summary>
    ''' <returns>List of database names</returns>
    Public Overridable Function GetDatabaseList() As Generic.List(Of String)
        Dim DatabaseList As New Generic.List(Of String)
        If OpenConnection() Then
            Dim dt As DataTable = Connection.GetSchema("DATABASES")
            Dim found As Boolean = False
            For Each dr As DataRow In dt.Rows
                DatabaseList.Add(dr("Database_Name").ToString)
            Next
            dt.Dispose()
            dt = Nothing
            Connection.Close()
        End If
        Return DatabaseList
    End Function

    ''' <summary>
    ''' Return a list of  tables for this project (converted to Proper case)
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overridable Function GetTableList() As Generic.List(Of String)
        Dim col As New Generic.List(Of String)
        LastException = Nothing
        Try
            If Schema_Tables Is Nothing AndAlso Not OpenConnection() Then Return col

            If dbDatabaseName = "" OrElse dbType = enumDBType.MySQL OrElse dbType = enumDBType.Oracle Or dbType = enumDBType.SQL_Server Then
                If Schema_Tables Is Nothing Then Schema_Tables = dbConnection.GetSchema("Tables")
            Else
                If Schema_Tables Is Nothing Then Schema_Tables = dbConnection.GetSchema("Tables", New String() {dbDatabaseName, TempDBName})
            End If

            For Each row As DataRow In Schema_Tables.Rows
                Dim TableName As String = row.Item("Table_Name")
                Dim IsUserTable As Boolean
                Select Case DatabaseType
                    Case clsDB.enumDBType.Oracle
                        IsUserTable = DatabaseName.Equals(row.Item("Owner").ToString, StringComparison.OrdinalIgnoreCase) 'schema name and dba are the same
                    Case Else
                        Dim TableType As String = row.Item("Table_Type")
                        IsUserTable = TableType.Equals("TABLE", StringComparison.OrdinalIgnoreCase) Or TableType.Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase)
                End Select
                If IsUserTable Then col.Add(Proper(TableName))
            Next
            Return col
        Catch ex As Exception
            LastException = ex
            Return col
            Throw
        Finally
            If dbConnection IsNot Nothing Then dbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Using object naming rules for each provider, take input object name and return suggested name that meets rules
    ''' </summary>
    ''' <param name="ObjectName">Table, column, etc. name</param>
    ''' <returns>Corrected name</returns>
    Public Function GetValidObjectName(ObjectName As String) As String
        Dim InvalidChar As String = ""
        Dim MaxLen As Integer = 0
        Dim AllowFirstNumeric As Boolean = False
        Dim AllowAllNumeric As Boolean = False

        ObjectName = RemoveBrackets(ObjectName)

        Select Case dbType
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                InvalidChar = "`~!%^&*()-=+[{]}\|;:'"",<.>/? "
                MaxLen = 31
                AllowFirstNumeric = False
                AllowAllNumeric = False
            Case enumDBType.SQLite
                'unable to find good info on this--based on guess and some testing
                InvalidChar = "`~!%^&*()-=+[{]}\|;:'"",<.>/? "
                MaxLen = 128
                AllowFirstNumeric = False
                AllowAllNumeric = False
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                InvalidChar = "`~!%^&*()-=+[{]}\|;:'"",<.>/? "
                MaxLen = 128
                AllowFirstNumeric = False
                AllowAllNumeric = False
            Case enumDBType.Oracle
                InvalidChar = "`~!@#$%^&*()-=+[{]}\|;:'"",<.>/? " 'only allow alphanumeric and underscores
                MaxLen = 30
                AllowFirstNumeric = False
                AllowAllNumeric = False
            Case enumDBType.MySQL
                InvalidChar = "`~!%^&*()-=+[{]}\|;:'"",<.>/? "
                MaxLen = 64
                AllowFirstNumeric = True
                AllowAllNumeric = False
            Case enumDBType.Access, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                InvalidChar = "`|#*.!"
                MaxLen = 64
                AllowFirstNumeric = True
                AllowAllNumeric = True
            Case enumDBType.DBase
                InvalidChar = "`|#*.! "
                MaxLen = 10
                AllowFirstNumeric = True
                AllowAllNumeric = True
            Case enumDBType.Paradox
                InvalidChar = "`|#*.!)("
                MaxLen = 25
                AllowFirstNumeric = True
                AllowAllNumeric = True
        End Select

        ObjectName = ObjectName.Trim
        If ObjectName = "" Then ObjectName = "NewTable"

        'replace invalid characters
        For Each c As Char In InvalidChar.ToCharArray
            ObjectName = ObjectName.Replace(c, "_")
        Next

        'prevent it from being too long
        ObjectName = ObjectName.Substring(0, Math.Min(ObjectName.Length, MaxLen))

        'see if first character is numeric
        If AllowFirstNumeric Then
            'see if all characters are numeric
            If Not AllowAllNumeric Then
                If IsNumeric(ObjectName) Then ObjectName = "A" & ObjectName
            End If
        Else
            Dim firstchar As String = ObjectName.Substring(0, 1).ToUpper
            If firstchar.ToUpper < "A" Or firstchar.ToUpper > "Z" Then ObjectName = "A" & ObjectName
        End If

        'don't let it be a reserved word
        If GetReservedWords.Contains(ObjectName.ToUpper) Then
            Select Case DatabaseType
                Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.Oracle
                    ObjectName = """" & ObjectName & """"
                Case enumDBType.MySQL
                    ObjectName = "`" & ObjectName & "`"
                Case Else
                    ObjectName = "[" & ObjectName & "]"
            End Select
        End If

        Return ObjectName
    End Function

    ''' <summary>
    ''' Interpret each provider's rules to return an automatically generated valid table name
    ''' </summary>
    ''' <param name="TableName">Desired table name</param>
    ''' <returns>Corrected tablename, with illegal characters substituted by _</returns>
    Public Function GetValidTableName(TableName As String) As String
        If TableName.StartsWith(TempDBName, StringComparison.OrdinalIgnoreCase) Then Return TableName 'is reference to temporary database; assume is valid tablename
        Dim NewTableName As String = GetValidObjectName(TableName)
        'remove single tick marks
        NewTableName = NewTableName.Replace("'", "")
        Return NewTableName
    End Function

    ''' <summary>
    ''' Return a list of views for this project
    ''' </summary>
    ''' <returns>List of view names</returns>
    Public Overridable Function GetViewList() As Generic.List(Of String)
        Try
            LastException = Nothing
            If Not OpenConnection() Then Return Nothing

            If dbType = enumDBType.SQL_Server_CE Or dbType = enumDBType.Excel Or dbType = enumDBType.CSV Or dbType = enumDBType.DBase Or dbType = enumDBType.Paradox Or dbType = enumDBType.Text Then
                Return New List(Of String) 'empty list; does not support views
            ElseIf dbDatabaseName = "" OrElse dbType = enumDBType.MySQL OrElse dbType = enumDBType.Oracle Then
                If Schema_Views Is Nothing Then Schema_Views = dbConnection.GetSchema("Views")
            Else
                If Schema_Views Is Nothing Then Schema_Views = dbConnection.GetSchema("Views", New String() {dbDatabaseName})
            End If

            Dim colView As String = ""
            Select Case dbType
                Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.Oracle
                    colView = "View_Name"
                Case Else
                    colView = "Table_Name"
            End Select

            Dim lst As New Generic.List(Of String)
            For Each row As DataRow In Schema_Views.Rows
                Dim ViewName As String = row.Item(colView)
                lst.Add(Proper(ViewName))
            Next
            Return lst
        Catch ex As Exception
            LastException = ex
            Throw
            Return Nothing
        Finally
            If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
        End Try
    End Function

    Public Function GetTableViewList() As List(Of String)
        Dim lst As List(Of String) = GetTableList()
        lst.AddRange(GetViewList)
        Return lst
    End Function

    ''' <summary>
    ''' Import data from tab-delimited file into table; if table exists, will be emptied first (or updated), if not will be created
    ''' The file format is assumed to be identical to the export format (i.e., tab-delimited with first column containing WRDB
    ''' field names). The field names don't need to be in a specific order, and extra and missing fields are handled properly.
    ''' </summary>
    ''' <param name="TableName">New table to import data into</param>
    ''' <param name="ImportFileName">Tab-delimited text file to import data from</param>
    ''' <param name="dtTemplate">Data table containing structure of table to import to</param> 
    ''' <param name="Append_Update">If false, existing table will be emptied first; otherwise will be appended to or replaced on record-by-record basis (if indexed)</param>
    ''' <param name="AppendOnly">If Append_Update is true and AppendOnly is true, will not replace existing records with new records</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>
    ''' Override for Paradox and Excel; if RECID field exists in table, will be preserved and auto-updated; if not, will not be added (unless is Paradox);
    ''' Note that import/export files are always culture neutral meaning that format will be same regardless of culture setting on computer 
    ''' so will not have a problem if backups are created in one language (number format) but restored in another. The down side is that exported
    ''' text files may not be as useful for import to Excel, etc.</remarks>
    Public Overridable Function ImportTable(ByRef TableName As String,
                                            ImportFileName As String,
                                            dtTemplate As DataTable,
                                            Optional Append_Update As Boolean = False,
                                            Optional AppendOnly As Boolean = False) As Boolean
        Dim sr As IO.StreamReader = Nothing
        Dim sw As IO.StreamWriter = Nothing
        Dim trans As DbTransaction = Nothing
        Dim tfp As FileIO.TextFieldParser = Nothing

        Try
            'results of tests:
            '- wide variation in import speed depending on provider 
            '- use of parameters & prepare increases speed for access, msde,paradox, decreases for sqlite and firebird
            '- removing primary key appears to have no impact on performance

            'so can determine total import time...
            Dim StartTime As Integer = My.Computer.Clock.TickCount

            'reset global flag
            Cancelled = False

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' initialize import file
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            'open importfile and get fieldnames
            If Not My.Computer.FileSystem.FileExists(ImportFileName) Then Return False

            'see if import file has only a single header line; if so just return True 
            '(however, if table does not exist, create it and leave it empty so when restore from archive will create empty tables)
            sr = New IO.StreamReader(ImportFileName)

            sr.ReadLine()
            If sr.EndOfStream And TableExists(TableName) Then
                sr.Close()
                Return True
            End If

            'reopen and read header line
            sr.Close()
            sr = New IO.StreamReader(ImportFileName)

            'following can handle case where delimiters are contained within quotes
            tfp = New FileIO.TextFieldParser(sr)
            tfp.TextFieldType = FileIO.FieldType.Delimited
            tfp.Delimiters = New String() {vbTab}
            tfp.CommentTokens = New String() {"#", ";"}
            tfp.TrimWhiteSpace = True
            tfp.HasFieldsEnclosedInQuotes = True

            'Dim NextLine As String = sr.ReadLine() 'read the header line and save fieldnames
            'If NextLine Is Nothing Then Return True
            If tfp.EndOfData Then Return True

            Dim ImportFieldNames As New Generic.List(Of String)
            'ImportFieldNames.AddRange(NextLine.ToUpper.Split(vbTab))
            ImportFieldNames.AddRange(tfp.ReadFields)
            For i = 0 To ImportFieldNames.Count - 1
                ImportFieldNames(i) = ImportFieldNames(i).ToUpper
            Next

            Dim NumBytes As Long = sr.BaseStream.Length

            'create error log file in temp directory and write column headings...

            Dim ErrorsFound As Boolean = False
            If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)
            Dim ErrorFile As String = String.Format("{0}\{1}_Errors.txt", TempPath, TableName)
            sw = New IO.StreamWriter(ErrorFile, False)
            sw.Write("Line Number" & vbTab)
            For Each s In ImportFieldNames
                sw.Write(s & vbTab)
            Next
            sw.WriteLine("Error Message")

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' initialize table
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            'make sure table name is valid for this provider
            TableName = GetValidTableName(TableName)
            If TableName = "" Then Return False

            'if not appending/updating, delete table if it exists then recreate (NO, delete all records)
            If Not Append_Update Then
                If Not TableExists(TableName) Then
                    If Not CreateTable(TableName, dtTemplate) Then Return False
                Else
                    RaiseProgress(0, 100, String.Format("Clearing data from {0}...", TableName))
                    ExecuteNonQuery("DELETE FROM {0}", FullTableName(TableName))
                End If
            Else
                If Not TableExists(TableName) Then If Not CreateTable(TableName, dtTemplate) Then Return False
            End If

            'set flag to determine if table is indexed
            Dim IsIndexed As Boolean = IsTableIndexed(TableName)

            Dim LineNum As Integer = 1
            RaiseProgress(0, 100, String.Format("Importing data into {0}...", TableName))

            Dim TableFieldNames As Generic.List(Of String) = GetColumnList(TableName)

            'if table has special column used by data tables (RecID) then treat this as auto-increment
            'column; if appending then find largest existing value and increment
            Dim NextRecID As Integer = 1
            If Append_Update And TableFieldNames.Contains("RECID") Then
                NextRecID = TestNull(ExecuteScalar("SELECT MAX(RecID) FROM {0}", FullTableName(TableName)), 0) + 1
            End If

            'remove blob column
            'If TableFieldNames.Contains("OBJECT_VALUE") Then TableFieldNames.Remove("OBJECT_VALUE")

            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' map table fields to those in import file
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            Dim TemplateFieldNames As New Generic.List(Of String)
            Dim PrimaryKeyFields As New Generic.List(Of String)

            For i As Int16 = 0 To dtTemplate.Columns.Count - 1
                With dtTemplate.Columns(i)
                    TemplateFieldNames.Add(GetValidObjectName(.ColumnName.ToUpper))
                    If IsPrimaryKey(dtTemplate, i) Then PrimaryKeyFields.Add(.ColumnName.ToUpper)
                End With
            Next

            'see if import file has special field name required to be able to read BLOB data

            Dim ObjectTypeColNum As Integer = -1
            Dim ObjectValueColNum As Integer = -1
            For Each fld As String In TemplateFieldNames
                If String.Equals(fld, "Object_Type", StringComparison.OrdinalIgnoreCase) Then ObjectTypeColNum = TemplateFieldNames.IndexOf(fld)
                If String.Equals(fld, "Object_Value", StringComparison.OrdinalIgnoreCase) Then ObjectValueColNum = TemplateFieldNames.IndexOf(fld)
            Next

            'map import columns to fields (ordinal value (field number) of column in standard template for each field in import file)
            'this will allow the order of the columns in the import file to be different than the table

            'Import_TemplateFieldNums contains column number in template associated with each import field
            Dim Import_TemplateFieldNums(ImportFieldNames.Count - 1) As Integer

            'predetermined flags about whether import fields are primary keys
            Dim IsImportPrimaryKeyField(ImportFieldNames.Count - 1) As Boolean
            Dim Import_FieldTypes(ImportFieldNames.Count - 1) As System.Type
            For i As Integer = 0 To Import_TemplateFieldNums.Length - 1
                Import_TemplateFieldNums(i) = TemplateFieldNames.IndexOf(GetValidObjectName(ImportFieldNames(i)))
                If Import_TemplateFieldNums(i) <> -1 Then
                    IsImportPrimaryKeyField(i) = IsPrimaryKey(dtTemplate, Import_TemplateFieldNums(i))
                    Import_FieldTypes(i) = dtTemplate.Columns(Import_TemplateFieldNums(i)).DataType
                End If
            Next

            'Table_TemplateFieldNums contains column number in template associated with each table field
            Dim Table_TemplateFieldNums(TableFieldNames.Count - 1) As Integer
            Dim IsTablePrimaryKeyField(TableFieldNames.Count - 1) As Boolean
            For i As Integer = 0 To Table_TemplateFieldNums.Length - 1
                Table_TemplateFieldNums(i) = TemplateFieldNames.IndexOf(GetValidObjectName(TableFieldNames(i)))
                If Table_TemplateFieldNums(i) = -1 Then
                    Throw New ApplicationException(String.Format("An invalid field name ({0}) was found in the import file ({1}); import operation was aborted.", TableFieldNames(i), TableName))
                End If
            Next

            'Import_TemplateFieldNums contains column number in table associated with each import field
            Dim Import_TableFieldNums(ImportFieldNames.Count - 1) As Integer
            For i As Integer = 0 To Import_TableFieldNums.Length - 1
                If ImportFieldNames(i) = "RECID" Then
                    Import_TableFieldNums(i) = -1 'never match to RecID--want to autoincrement
                Else
                    Import_TableFieldNums(i) = TableFieldNames.IndexOf(GetValidObjectName(ImportFieldNames(i)))
                End If
            Next

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' set up connection and transaction, and build sql statements
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            If dbConnection.State <> ConnectionState.Open Then dbConnection.Open()
            trans = dbConnection.BeginTransaction

            Dim cmdInsert As DbCommand = dbConnection.CreateCommand
            Dim cmdUpdate As DbCommand = dbConnection.CreateCommand
            Dim cmdSelect As DbCommand = dbConnection.CreateCommand

            Dim SQLInsert As String = String.Format("INSERT INTO {0} ({1}) VALUES (", FullTableName(TableName), ArrayToCSV(TableFieldNames.ToArray))
            Dim SQLUpdate As String = String.Format("UPDATE {0} SET ", FullTableName(TableName))
            Dim SQLSelect As String = String.Format("SELECT COUNT(*) FROM {0} ", FullTableName(TableName))
            Dim SQLWhere As String = ""
            Dim WhereParams As New Generic.List(Of DbParameter)
            'for some reason, cannot use same parameter in two collections for some providers, make copy here
            Dim WhereParams2 As New Generic.List(Of DbParameter)

            'one parameter for each field in table

            For i As Integer = 0 To TableFieldNames.Count - 1
                With dtTemplate.Columns(Table_TemplateFieldNums(i))
                    Dim ParamName As String = ParameterName(.ColumnName).ToUpper
                    cmdInsert.Parameters.Add(Parameter(cmdInsert, ParamName, .DataType, .MaxLength))
                    cmdUpdate.Parameters.Add(Parameter(cmdUpdate, ParamName, .DataType, .MaxLength))

                    SQLInsert &= IIf(i = 0, "", ", ") & ParamName
                    SQLUpdate &= IIf(i = 0, "", ", ") & .ColumnName & "=" & ParamName

                    If ImportFieldNames.Contains(TableFieldNames(i)) AndAlso PrimaryKeyFields.Contains(TableFieldNames(i)) Then 'is key field and exists in import file, build where clause and save parameters for where clause (may be positional parameters (?) so order will be important)
                        SQLWhere &= IIf(SQLWhere = "", " WHERE ", " AND ") & .ColumnName & " = " & ParamName
                        WhereParams.Add(Me.Parameter(cmdUpdate, ParamName & "_Key", .DataType, .MaxLength))
                        WhereParams2.Add(Me.Parameter(cmdSelect, ParamName, .DataType, .MaxLength))
                    End If
                End With
            Next

            'now add saved "Where" parameters to end of collection in case are positional
            For Each Param As DbParameter In WhereParams
                cmdUpdate.Parameters.Add(Param)
            Next
            For Each Param As DbParameter In WhereParams2
                cmdSelect.Parameters.Add(Param)
            Next
            SQLInsert &= ")"
            SQLUpdate &= SQLWhere
            SQLSelect &= SQLWhere

            With cmdInsert
                .Transaction = trans
                .Connection = dbConnection
                .CommandText = SQLInsert
                .Prepare()
            End With

            With cmdUpdate
                .Transaction = trans
                .Connection = dbConnection
                .CommandText = SQLUpdate
                .Prepare()
            End With

            With cmdSelect
                .Transaction = trans
                .Connection = dbConnection
                .CommandText = SQLSelect
                .Prepare()
            End With

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' start loop of importing lines from text file
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            'Dim ByteNum As Long = NextLine.Length + 2
            Dim ByteNum As Long = sr.BaseStream.Position
            Dim StdDateFmt As Boolean = True
            Dim LastTime As Date = Now

            Do
                LineNum += 1

                'check for Excel...
                'No: driver returns good error message for XLS so skip this

                'If IO.Path.GetExtension(DataSource).ToUpper = ".XLS" And DatabaseType = enumDBType.Excel And LineNum >= UInt16.MaxValue Then
                '    trans.Commit()
                '    trans.Dispose()
                '    trans = Nothing
                '    sr.Close()
                '    sr.Dispose()
                '    dbConnection.Close()
                '    RaiseEvent Progress(Me, 100, 100, "")
                '    Throw New DbSqlException(String.Format("The maximum number or records in an Excel spreadsheet has been exceeded ({0}) so the import has been aborted.", UInt16.MaxValue))
                'End If

                'read next line
                'NextLine = sr.ReadLine

                'if blank line, skip and continue
                'If String.IsNullOrEmpty(NextLine) OrElse NextLine.Replace(vbTab, "").Trim = "" Then Continue Do

                'if end of file character, exit
                'If NextLine.Substring(0, 1) = Chr(26) Then Exit Do

                If tfp.EndOfData Then Exit Do

                'keep track of bytes read for progress
                'ByteNum += NextLine.Length + 1
                ByteNum = sr.BaseStream.Position

                'initialize parameters to default values unless is keyfield
                Dim paramNum As Integer = 0
                For i As Integer = 0 To TableFieldNames.Count - 1
                    With dtTemplate.Columns(Table_TemplateFieldNums(i))
                        If .ColumnName.ToUpper = "RECID" Then
                            cmdInsert.Parameters(paramNum).Value = NextRecID
                        Else
                            cmdInsert.Parameters(paramNum).Value = IIf(IsTablePrimaryKeyField(i), "", TestNull(.DefaultValue, DBNull.Value))
                        End If
                        If .MaxLength > 0 Then cmdInsert.Parameters(paramNum).Size = .MaxLength
                        cmdUpdate.Parameters(paramNum).Value = cmdInsert.Parameters(paramNum).Value
                        If .MaxLength > 0 Then cmdUpdate.Parameters(paramNum).Size = .MaxLength
                        paramNum += 1
                    End With
                Next i

                Try
                    'Dim ImportItems() As String = NextLine.Split(vbTab)
                    Dim ImportItems() As String = tfp.ReadFields
                    'If ImportItems.Length <> ImportFieldNames.Count Then Throw New DbSqlException("Invalid number of columns in " & NextLine)
                    WhereParams.Clear()
                    WhereParams2.Clear()
                    Dim HasPrimaryKeyField As Boolean = False
                    Dim BaseDate As Date = Date.MinValue

                    Dim ObjectType As String = "" 'only used for tables having an Object_Type field as well as blob field

                    'place imported values on this line in parameters...

                    For i As Integer = 0 To ImportItems.Length - 1

                        'if this column is not a table field, ignore it
                        If Import_TableFieldNums(i) = -1 Then Continue For

                        If (ImportItems(i) = "" AndAlso Not IsImportPrimaryKeyField(i)) OrElse String.Equals(ImportItems(i).ToUpper, "<NULL>", StringComparison.OrdinalIgnoreCase) Then
                            'assign as null value
                            cmdInsert.Parameters(Import_TableFieldNums(i)).Value = DBNull.Value
                        Else
                            If Import_FieldTypes(i) Is GetType(Date) Then
                                Dim dt As Date = Nothing
                                If Date.TryParse(ImportItems(i), Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dt) Then
                                    cmdInsert.Parameters(Import_TableFieldNums(i)).Value = dt
                                Else
                                    cmdInsert.Parameters(Import_TableFieldNums(i)).Value = DBNull.Value
                                End If
                                'If IsDate(ImportItems(i)) Then
                                '    BaseDate = ImportItems(i)
                                '    cmdInsert.Parameters(Import_TableFieldNums(i)).Value = CDate(ImportItems(i))
                                'Else
                                '    cmdInsert.Parameters(Import_TableFieldNums(i)).Value = DBNull.Value
                                'End If
                            ElseIf Import_FieldTypes(i) Is GetType(Boolean) Then
                                If ImportItems(i).StartsWith("T", StringComparison.OrdinalIgnoreCase) Or ImportItems(i) = "1" Then
                                    ImportItems(i) = "True"
                                ElseIf ImportItems(i).StartsWith("F", StringComparison.OrdinalIgnoreCase) Or ImportItems(i) = "0" Then
                                    ImportItems(i) = "False"
                                Else
                                    ImportItems(i) = ""
                                End If
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(CBool(ImportItems(i)), DBNull.Value)
                            ElseIf Import_FieldTypes(i) Is GetType(Integer) Then
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(Integer.Parse(ImportItems(i), Globalization.CultureInfo.InvariantCulture), DBNull.Value)
                            ElseIf Import_FieldTypes(i) Is GetType(Double) Then
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(Double.Parse(ImportItems(i), Globalization.CultureInfo.InvariantCulture), DBNull.Value)
                            ElseIf Import_FieldTypes(i) Is GetType(Single) Then
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(Single.Parse(ImportItems(i), Globalization.CultureInfo.InvariantCulture), DBNull.Value)
                            ElseIf Import_FieldTypes(i) Is GetType(String) Then 'don't let it exceed field length
                                ImportItems(i) = ImportItems(i).Replace("'", "") 'prevent single ticks from being imported; may screw up later SQL queries
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(Strings.Left(ImportItems(i), cmdInsert.Parameters(Import_TableFieldNums(i)).Size), "")
                            Else
                                cmdInsert.Parameters(Import_TableFieldNums(i)).Value = TestNull(ImportItems(i), DBNull.Value)
                            End If
                        End If

                        'duplicate parameter defaults for insert into update

                        cmdUpdate.Parameters(Import_TableFieldNums(i)).Value = cmdInsert.Parameters(Import_TableFieldNums(i)).Value
                        If IsImportPrimaryKeyField(i) Then
                            WhereParams.Add(cmdUpdate.Parameters(Import_TableFieldNums(i)))
                            WhereParams2.Add(cmdUpdate.Parameters(Import_TableFieldNums(i)))
                            HasPrimaryKeyField = True
                        End If
                    Next

                    If ObjectTypeColNum <> -1 And ObjectValueColNum <> -1 Then
                        'has blob data; try to load data from separate file in same directory with same name (plus record number) but having extension specified in special field 
                        'get file extension from previously imported field on this line
                        Dim ext As String = TestNull(cmdInsert.Parameters(ObjectTypeColNum).Value, "")
                        Dim ObjType As clsBlob.enumObjectType = clsBlob.ObjectType(ext)
                        Dim NumTsRec As Integer = 0
                        Dim blobfile As String = String.Format("{0}\{1}{2}.{3}", IO.Path.GetDirectoryName(ImportFileName), IO.Path.GetFileNameWithoutExtension(ImportFileName), LineNum - 2, ext)
                        Dim blob() As Byte = clsBlob.LoadBlob(ObjType, blobfile, BaseDate, NumTsRec)

                        If blob IsNot Nothing Then
                            cmdInsert.Parameters(ObjectTypeColNum).Value = clsBlob.ObjectName(ObjType)
                            cmdInsert.Parameters(ObjectValueColNum).Value = blob
                        Else
                            cmdInsert.Parameters(ObjectTypeColNum).Value = DBNull.Value
                            cmdInsert.Parameters(ObjectValueColNum).Value = DBNull.Value
                        End If
                    End If

                    'For i As Integer = 0 To cmdInsert.Parameters.Count - 1
                    '    With cmdInsert.Parameters(i)
                    '        Debug.Print(.ParameterName & ":" & .Value)
                    '    End With
                    'Next

                    If Append_Update AndAlso IsIndexed AndAlso WhereParams2.Count > 0 AndAlso HasPrimaryKeyField Then

                        'see how many matching records already exist in table
                        'cmdSelect.Parameters.Clear()
                        'For Each param As DbParameter In WhereParams2
                        '    cmdSelect.Parameters.Add(param)
                        'Next
                        For i As Integer = 0 To WhereParams2.Count - 1
                            cmdSelect.Parameters(i).Value = WhereParams2(i).Value
                        Next

                        Dim numFound As Integer = cmdSelect.ExecuteScalar

                        If AppendOnly Then
                            If numFound = 0 Then cmdInsert.ExecuteNonQuery()
                        Else
                            Dim cmdUpdateText As String = cmdUpdate.CommandText

                            If numFound = 0 Then 'must insert
                                cmdInsert.ExecuteNonQuery()
                            Else 'must update
                                cmdUpdate.ExecuteNonQuery()
                            End If

                            'reset commandtext in case it changed above
                            Try
                                cmdUpdate.CommandText = cmdUpdateText
                            Catch ex As Exception
                                Debug.Assert(False)
                            End Try
                        End If
                    Else
                        cmdInsert.ExecuteNonQuery()
                    End If

                    'want to commit more often than once, or Oracle undo buffer in some providers will overflow!
                    'following requires to start a new transaction; if an error occurs or user cancels, will not be able to undo the entire import
                    If DatabaseType = enumDBType.Oracle AndAlso LineNum Mod 5000 = 0 Then
                        trans.Commit()
                        trans = dbConnection.BeginTransaction
                    End If

                Catch ex As FileIO.MalformedLineException
                    LastException = ex
                    ErrorsFound = True
                    sw.WriteLine(String.Format("{0}\t{1}\t{2}", LineNum, tfp.ErrorLine, ex.Message).Replace("\t", vbTab))
                Catch ex As Exception
                    LastException = ex
                    ErrorsFound = True
                    sw.WriteLine(String.Format("{0}\t{1}\t{2}", LineNum, "Unexpected error", ex.Message).Replace("\t", vbTab))
                Finally
                    If Now.Subtract(LastTime).TotalSeconds > 0.5 Then
                        RaiseProgress(ByteNum, NumBytes, String.Format("Importing line {0:N0} into {1}", LineNum, TableName))
                    End If
                End Try

                If Cancelled Then
                    sr.Close()
                    sr.Dispose()
                    sw.Close()
                    sw.Dispose()
                    trans.Rollback()
                    trans.Dispose()
                    trans = Nothing
                    dbConnection.Close()
                    Return False
                End If

                NextRecID += 1

            Loop Until tfp.EndOfData 'sr.EndOfStream

            sw.Close()
            If ErrorsFound Then
                Process.Start(ErrorFile)
            End If

            Try
                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                Throw
            End Try

            trans.Dispose()
            trans = Nothing
            sr.Close()
            sr.Dispose()
            dbConnection.Close()

            'in case have imported data into table having Create_Update field, set all null values to current date-time
            UpdateRecordTimeStamp(TableName)

            RaiseEvent Progress(Me, 100, 100, "")
            Dim NumRec As Integer = LineNum - 2
            Dim NumSec As Double = (My.Computer.Clock.TickCount - StartTime) / 1000.0
            'warningmsg(String.Format("A total of {0} records were imported into {1}; the operation required {2:0.00} seconds; the transfer rate was {3:0} records per second", NumRec, TableName, NumSec, NumRec / NumSec), "Import Table", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If trans IsNot Nothing Then trans.Dispose()
            trans = Nothing
            If sr IsNot Nothing Then
                sr.Close()
                sr.Dispose()
            End If
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            dbConnection.Close()
            RaiseEvent Progress(Me, 100, 100, "")
        End Try
    End Function

    ''' <summary>
    ''' Determine is specified column is a primary key
    ''' </summary>
    ''' <param name="dt">Data table containing structure</param>
    ''' <param name="columnNum">Index of column being checked</param>
    ''' <returns>True is if primary key column</returns>
    Public Function IsPrimaryKey(dt As DataTable, columnNum As Integer) As Boolean
        Return columnNum <= dt.Columns.Count - 1 AndAlso IsPrimaryKey(dt, dt.Columns(columnNum))
    End Function

    ''' <summary>
    ''' Determine is specified column is a primary key
    ''' </summary>
    ''' <param name="dt">Data table containing structure</param>
    ''' <param name="column">Column being checked</param>
    ''' <returns>True is if primary key column</returns>
    Public Function IsPrimaryKey(dt As DataTable, column As DataColumn) As Boolean
        With dt
            Return Array.IndexOf(.PrimaryKey, column) <> -1
        End With
    End Function

    Public Overridable Function OpenConnection() As Boolean
        LastException = Nothing
        While dbConnection.State = ConnectionState.Connecting
            Threading.Thread.Sleep(500)
        End While
        If dbConnection.State <> ConnectionState.Open Then
            Try
                dbConnection.Open()
                Return True
            Catch ex As Exception
                With dbConnection
                    LastException = New DbSqlException("Unable to open connection to database; this may be due to an outage on the database server, entry of an invalid password, or if using a file-based database, the database file being opened by another user (or yourself)." & vbCrLf & vbCrLf &
                                                       String.Format("Server: {0}, Database: {1}, Connection String: {2}, State: {3}",
                                                                     .DataSource, .Database, .ConnectionString, .State), ex)
                End With
                'Throw New DbSqlException("Unable to open connection to database; this may be due to an outage on the database server, entry of an invalid password, or if using a file-based database, the database file being opened by another user (or yourself)." & IIf(dbType = enumDBType.Paradox, " For Paradox tables, make sure you have the Borland Database Engine (BDE) installed (if it is, there will be an entry in the Control Panel). If it is not, you can install the BDE using SetupBDE.bat to be found in the WRDB program folder. If you intend to use WRDB as a non-administrative user, be sure to use the Borland Database Administrator in the Control Panel and browse to Configure | Drivers | Native | Paradox | NET DIR and browse to a directory for which you have full privileges.", ""), ex)
                Return False
            End Try
        Else
            Return True
        End If
    End Function

    Public Sub CloseConnection()
        If dbConnection IsNot Nothing Then
            If dbConnection.State <> ConnectionState.Closed Then dbConnection.Close()
        End If
    End Sub

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public MustOverride Function Parameter(ByRef Command As DbCommand, ParameterName As String, DataType As System.Type, Optional Size As Int16 = 0) As DbParameter

    ''' <summary>
    ''' Return name of parameter used in SQL queries
    ''' </summary>
    ''' <param name="ColumnName">Name of column</param>
    ''' <returns>Parameter name</returns>
    ''' <remarks>Already handled for all provider types except Other</remarks>
    Public Overridable Function ParameterName(ColumnName As String) As String
        Select Case Me.dbType
            Case enumDBType.Access, enumDBType.Paradox, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV
                Return "?"
            Case enumDBType.Oracle
                Return ":" & ColumnName
            Case enumDBType.SQL_Server, enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.SQLite, enumDBType.SQL_Server_CE
                Return "@" & ColumnName
            Case enumDBType.MySQL
                Return "?" & ColumnName
            Case Else
                Debug.Assert(False)
                Return ""
        End Select
    End Function

    ''' <summary>
    ''' Add brackets that may be needed to allow reserved words for field or tablenames
    ''' </summary>
    ''' <param name="fieldlist">Table or field name (or can pass comma-separated list of fields)</param>
    Public Function AddBrackets(fieldlist As String) As String
        If fieldlist.StartsWith("(") Then Return fieldlist 'may be subselect stored in tablename
        Dim ar() As String
        If fieldlist.Contains(",") Then 'is list of fields
            ar = fieldlist.Split(",")
        Else 'is possibly table name which may be fully qualifies like dbname.dbo.tablename
            ar = fieldlist.Split(".")
        End If
        Dim fieldlist2 As String = ""
        For i As Integer = 0 To ar.Length - 1
            If i > 0 Then fieldlist2 &= IIf(fieldlist.Contains(","), ",", ".")
            ar(i) = RemoveBrackets(ar(i))
            Select Case DatabaseType
                Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.Oracle
                    fieldlist2 &= """" & ar(i).ToUpper & """"
                Case enumDBType.MySQL
                    fieldlist2 &= "`" & ar(i) & "`"
                Case Else
                    fieldlist2 &= "[" & ar(i) & "]"
            End Select
        Next
        Return fieldlist2
    End Function

    ''' <summary>
    ''' Remove brackets that may have been placed around tablenames to facilitate SQL queries.
    ''' </summary>
    ''' <param name="s">Table name that may contain brackets</param>
    ''' <remarks>This is needed to get filename back for some file operations</remarks>
    Public Function RemoveBrackets(s As String) As String
        If String.IsNullOrEmpty(s) Then Return s
        Select Case DatabaseType
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.Oracle
                Return s.Replace("""", "")
            Case enumDBType.MySQL
                Return s.Replace("`", "")
            Case Else
                Return s.Replace("[", "").Replace("]", "")
        End Select
    End Function

    ''' <summary>
    ''' SQL fragment that can be used to return the record number in a table or view (only needed for Open External Database Table)
    ''' </summary>
    Public Function RecordID() As String
        Select Case dbType
            Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                Return "ROW_NUMBER() OVER (ORDER BY (SELECT 0))"
            Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                Return 0 'will be able to open the table but not do queries
            Case enumDBType.MySQL
                Return 0 'will be able to open the table but not do queries; there is actually a way to do this with variables, but requires more manipulation of sql statement
            Case enumDBType.Oracle
                Return "ROWNUM"
            Case Else
                Throw New ApplicationException("Invalid provider type passed to RecordID routine.")
        End Select
    End Function

    ''' <summary>
    ''' Rename table in current project
    ''' </summary>
    ''' <param name="OldName">Name of existing table (must already exist)</param>
    ''' <param name="NewName">New name for table (must not already exist)</param>
    ''' <param name="dtTemplate">Only needed for Firebird override since it can't to a rename</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Must override for SQL Server, Access, and Firebird</remarks>
    Public Overridable Function RenameTable(OldName As String, NewName As String, Optional dtTemplate As DataTable = Nothing) As Boolean
        Try
            If TableExists(NewName) Or Not TableExists(OldName) Then Return False
            Select Case DatabaseType
                Case enumDBType.Firebird, enumDBType.Firebird_Embedded
                    If dtTemplate Is Nothing Then Throw New Exception("The data table template must be passed to Firebird's RenameTable function.")
                    If Not CreateTable(NewName, dtTemplate) Then Return False
                    If AppendTable(NewName, OldName) Then
                        DropTable(OldName)
                        Return True
                    Else
                        Return False
                    End If
                Case enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                    Return ExecuteNonQuery("sp_RENAME '{0}','{1}'", OldName, NewName) <> -2
                Case enumDBType.Access
                    If ExecuteNonQuery("SELECT * INTO {0} FROM {1}", AddBrackets(NewName), AddBrackets(OldName)) <> -2 Then
                        DropTable(OldName)
                        Return True
                    Else
                        Return False
                    End If
                Case Else
                    Return ExecuteNonQuery("ALTER TABLE {0} RENAME TO {1}", OldName, NewName) <> -2
            End Select

            Schema_Columns = Nothing
            Schema_Indexes = Nothing
            Schema_IndexColumns = Nothing

        Catch ex As Exception
            LastException = ex
            Throw
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Given a datatable, instantiate a new database table
    ''' </summary>
    ''' <param name="TableName">Name of new table (if exists, will be overwritten)</param>
    ''' <param name="dt">Datatable to instantiate</param>
    ''' <returns>True if successful</returns>
    Public Function SaveTable(TableName As String, dt As DataTable) As Boolean
        Try
            Cancelled = False
            DropTable(TableName)
            If Not CreateTable(TableName, dt) Then Return False

            Connection.Open()
            Dim trans As DbTransaction = Connection.BeginTransaction
            Dim cmdInsert As DbCommand = Connection.CreateCommand
            With cmdInsert
                .Transaction = trans
                .Connection = Connection
            End With
            For r As Integer = 0 To dt.Rows.Count - 1
                Dim sbSQL As New System.Text.StringBuilder(String.Format("INSERT INTO {0} VALUES (", FullTableName(TableName)))
                For Each c As DataColumn In dt.Columns
                    If c.ColumnName.ToUpper = "RECID" And Not IsPrimaryKeyRequired() Then Continue For
                    If Not sbSQL.ToString.EndsWith("(") Then sbSQL.Append(",")
                    If c.DataType Is GetType(String) Then
                        sbSQL.Append(String.Format("'{0}'", dt.Rows(r).Item(c).ToString.Replace("'", "''")))
                    ElseIf c.DataType Is GetType(Date) Then
                        If dt.Rows(r).IsNull(c) Then
                            sbSQL.Append("NULL")
                        Else
                            sbSQL.Append(DateTime(dt.Rows(r).Item(c)))
                        End If
                    ElseIf c.DataType Is GetType(Boolean) Then
                        If dt.Rows(r).IsNull(c) Then
                            sbSQL.Append("NULL")
                        Else
                            sbSQL.Append(String.Format("{0}", IIf(CBool(dt.Rows(r).Item(c)), 1, 0)))
                        End If
                    Else
                        If dt.Rows(r).IsNull(c) OrElse Single.IsNaN(dt.Rows(r).Item(c)) OrElse Double.IsNaN(dt.Rows(r).Item(c)) Then
                            sbSQL.Append("NULL")
                        Else
                            sbSQL.Append(String.Format(Globalization.CultureInfo.InvariantCulture, "{0}", dt.Rows(r).Item(c)))
                        End If
                    End If
                Next
                sbSQL.Append(")")
                cmdInsert.CommandText = sbSQL.ToString
                If cmdInsert.ExecuteNonQuery() = 0 Then Debug.Assert(False) : Return False
                'RaiseEvent Progress(r, dt.Rows.Count, "Writing to table...")
                If Cancelled Then
                    trans.Rollback()
                    trans.Dispose()
                    trans = Nothing
                    dt.Clear()
                    dt.Dispose()
                    dt = Nothing
                    Return False
                End If
                RaiseEvent Progress(Me, r, dt.Rows.Count - 1, "Saving " & TableName)
            Next
            trans.Commit()
            trans.Dispose()
            trans = Nothing
            Return True
        Catch ex As Exception
            LastException = ex
            Throw
            Return False
        End Try
    End Function

    Private Function SinglesToBytes(ar() As Single) As Byte()
        Dim b(ar.Length * 4 - 1) As Byte
        Buffer.BlockCopy(ar, 0, b, 0, b.Length)
        Return b
    End Function

    ''' <summary>
    ''' Convert semicolon-separated list of singles to byte array
    ''' </summary>
    ''' <param name="s"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function StringToBytes(s As String) As Byte()
        Dim ar() As String = s.Split(";")
        Dim sng(ar.Length) As Single
        For i As Integer = 0 To sng.Length - 1
            sng(i) = MyNumber(ar(i)) 'Convert.ToUInt64(s.Substring(i * 16, 16), 16)
        Next
        Dim b(ar.Length * 4 - 1) As Byte
        Buffer.BlockCopy(sng, 0, b, 0, b.Length)
        Return b
    End Function

    ''' <summary>
    ''' Determine if table exists in the database
    ''' </summary>
    ''' <param name="TblName">Name of table (case is ignored)</param>
    ''' <returns>True if exists</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overridable Function TableExists(TblName As String) As Boolean
        Return TblName.StartsWith("(") OrElse GetTableList.Contains(Proper(TableName(TblName)))
    End Function

    ''' <summary>
    ''' Determine if all tables exist in the database
    ''' </summary>
    ''' <param name="TableNames">Names of tables</param>
    ''' <returns>True if exists</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Function TableExists(TableNames() As String) As Boolean
        Dim tbllist As Generic.List(Of String) = GetTableList()
        For Each tbl As String In TableNames
            If Not tbllist.Contains(Proper(TableName(tbl))) Then Return False
        Next
        Return True
    End Function

    ''' <summary>
    ''' Determine if view exists in the database; if not (or if database type doesn't support views, returns false)
    ''' </summary>
    ''' <param name="ViewName">Name of view</param>
    ''' <returns>True if exists</returns>
    Public Overridable Function ViewExists(ViewName As String) As Boolean
        If ViewName.ToLower.Contains(".dbo.") Then Return True 'this is an external table or view---assume it is always there
        If Not SupportsViews() Then Return False
        Return GetViewList.Contains(Proper(TableName(ViewName))) 'use TableName function to strip off brackets and schema prefixes (Oracle)
    End Function

    ''' <summary>
    ''' Determine if view exists in the database; if not (or if database type doesn't support views, returns false)
    ''' </summary>
    ''' <param name="ViewNames">Names of views</param>
    ''' <returns>True if exists</returns>
    Public Overridable Function ViewExists(ViewNames() As String) As Boolean
        If Not SupportsViews() Then Return False
        Dim viewlist As Generic.List(Of String) = GetViewList()
        For Each view As String In ViewNames
            If Not viewlist.Contains(Proper(TableName(view))) Then Return False
        Next
        Return True
    End Function

    Public Function TableViewExists(TableViewName As String) As Boolean
        Return TableExists(TableViewName) Or ViewExists(TableViewName)
    End Function

    Public Function TableViewExists(TableViewNames() As String) As Boolean
        If Not SupportsViews() Then Return TableExists(TableViewNames)
        Dim tblviewlist As Generic.List(Of String) = GetTableList()
        tblviewlist.AddRange(GetViewList)
        For Each tbl As String In TableViewNames
            If Not tblviewlist.Contains(Proper(RemoveBrackets(tbl))) Then Return False
        Next
        Return True
    End Function

    ''' <summary>
    ''' Examine schema and determine if any columns are indexed
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>True if table is indexed</returns>
    Public Function IsTableIndexed(TableName As String) As Boolean
        Return GetIndexList(TableName).Count > 0
    End Function

    ''' <summary>
    ''' If table requires a primary key to be present to update the table, return true
    ''' </summary>
    Public Function IsPrimaryKeyRequired() As Boolean
        Select Case dbType
            Case enumDBType.Paradox, enumDBType.CSV, enumDBType.DBase, enumDBType.Excel, enumDBType.Text
                Return True
            Case Else
                Return False
        End Select
    End Function

    ''' <summary>
    ''' Try to connect to specified provider's database; return True if successful
    ''' </summary>
    Public Overridable Function TestConnection() As Boolean
        Try

            'see if database exists
            Dim extension As String = ""

            Select Case dbType
                Case clsDB.enumDBType.Firebird_Embedded, clsDB.enumDBType.Firebird : extension = ".fdb"
                Case clsDB.enumDBType.Access : extension = ".mdb"
                Case clsDB.enumDBType.SQLite : extension = ".sdb"
                Case enumDBType.SQL_Server_CE : extension = ".sdf"
                Case enumDBType.Excel : extension = ".xls"
            End Select

            Select Case dbType
                Case clsDB.enumDBType.Access, clsDB.enumDBType.Firebird_Embedded, clsDB.enumDBType.SQLite, enumDBType.Excel, enumDBType.SQL_Server_CE
                    If Not My.Computer.FileSystem.FileExists(dbDataSource) Then Return False
                    If dbConnection IsNot Nothing Then dbConnection.Close()
                    CreateConnection()
                    Try
                        dbConnection.Open()
                        Return True
                    Catch ex As Exception
                        LastException = ex
                        Throw New DbSqlException("Although the database file exists, WRDB was unable to open the connection. This may be due to an invalid password being used, or the database may be corrupted.", ex)
                    Finally
                        dbConnection.Close()
                    End Try
                Case clsDB.enumDBType.Paradox, enumDBType.DBase, enumDBType.Text, enumDBType.CSV
                    Return My.Computer.FileSystem.DirectoryExists(dbDataSource)
                Case clsDB.enumDBType.SQL_Server, enumDBType.Firebird, enumDBType.MySQL, enumDBType.Oracle
                    If dbConnection IsNot Nothing Then dbConnection.Close()
                    CreateConnection()
                    Try
                        Return OpenConnection()
                    Catch ex As Exception
                        LastException = ex
                        If dbType = enumDBType.Firebird Then
                            Throw New DbSqlException("Unable to connect to Firebird database on server. Note that you must create the database file on the root directory of the server using a database administrator tool like IBExpert. Thus, this database must be stored on the server as " & DatabaseName.Replace("\", "\\"), ex)
                        ElseIf dbType = enumDBType.Oracle Then
                            Throw New DbSqlException("Unable to connect to Oracle database on server. Note that you must also install Oracle Database Access Components (ODAC) libraries available from www.Oracle.com (search for 'download ODAC').", ex)
                        Else
                            'Throw
                        End If
                        Return False
                    Finally
                        CloseConnection()
                    End Try
                Case Else
                    Return False
            End Select
        Catch ex As Exception
            LastException = ex
            Throw
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Use background thread to more quickly timeout a connection attempt
    ''' </summary>
    ''' <param name="TimeOut">The amount of time in seconds before timeout occurs</param>
    ''' <remarks></remarks>
    Public Function TestConnectionQuick(TimeOut As Integer) As Boolean
        Dim sw As New Stopwatch
        Dim Success As Boolean = False
        Dim t As New Threading.Thread(
            Sub()
                Try
                    sw.Start()
                    Success = TestConnection()
                Catch ex As Exception
                End Try
            End Sub
            )
        t.IsBackground = True
        t.Start()
        While sw.ElapsedMilliseconds < TimeOut * 1000
            If t.Join(1) Then Exit While
        End While
        Return Success
    End Function

    ''' <summary>
    ''' Handle different syntax for the uppercase function in SQL
    ''' </summary>
    ''' <returns>Correct function name for converting to upper case</returns>
    Public Function UCase() As String
        Select Case DatabaseType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.DBase, enumDBType.Excel, enumDBType.Text, enumDBType.CSV : Return "UCASE"
            Case Else
                Return "UPPER"
        End Select
    End Function

    ''' <summary>
    ''' Form standard SQL for CASE WHEN THEN ELSE END sequence
    ''' </summary>
    ''' <param name="whenthenclauses">Pairs of when, then strings; if odd number, last parameter is else/default string</param>
    ''' <returns>Properly formatted SQL string</returns>
    Public Function CaseSQL(ParamArray whenthenclauses() As String) As String
        Dim IsAccess As Boolean = False
        Select Case DatabaseType
            Case enumDBType.Paradox, enumDBType.Access, enumDBType.CSV, enumDBType.DBase, enumDBType.Excel, enumDBType.Text
                IsAccess = True
        End Select

        Dim sql As String = ""
        For i As Integer = 0 To whenthenclauses.Length - 2 Step 2
            If IsAccess Then
                If i = 0 Then sql = "SWITCH ( " Else sql &= ", "
                sql &= whenthenclauses(i) & ", " & whenthenclauses(i + 1)
            Else
                If i = 0 Then sql = "CASE"
                sql &= " WHEN " & whenthenclauses(i) & " THEN " & whenthenclauses(i + 1)
            End If
        Next
        If whenthenclauses.Length Mod 2 <> 0 Then 'is odd number so ELSE clause
            If IsAccess Then
                sql &= ", True, " & whenthenclauses(whenthenclauses.Length - 1)
            Else
                sql &= " ELSE " & whenthenclauses(whenthenclauses.Length - 1)
            End If
        End If
        If IsAccess Then
            sql &= " )"
        Else
            sql &= " END"
        End If
        Return sql
    End Function

    Private Sub bwAddTable_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwAddTable.DoWork
        Try
            IsQueryBusy = True
            Dim colArg As Collection = e.Argument
            If bwAddTable.CancellationPending Then e.Cancel = True
            bwAddTableDataAdapter = AddTable(colArg(1), colArg(2), colArg(3))
            e.Result = bwAddTableDataAdapter
        Catch ex As Exception
            LastException = ex
            Throw New DbSqlException("Unable to add table.", ex)
        End Try
    End Sub

    Private Sub bwAddTable_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwAddTable.RunWorkerCompleted

        'does not fire this for some reason?????????????

        'bwAddTableDataAdapter = e.Result
        'If bwAddTable IsNot Nothing Then bwAddTable.Dispose()
        'bwAddTable = Nothing
        IsQueryBusy = False
    End Sub

    ''' <summary>
    ''' Starts query on background thread
    ''' </summary>
    Private Sub bwExecuteNonQuery_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwExecuteNonQuery.DoWork
        QuerySQL = CType(e.Argument, String)
        IsQueryBusy = True
        e.Result = ExecuteNonQuery(QuerySQL)
    End Sub

    ''' <summary>
    ''' Called when background thread is complete
    ''' </summary>
    Private Sub bwExecuteNonQuery_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwExecuteNonQuery.RunWorkerCompleted
        RaiseEvent Progress(Me, 100, 100, "")
        If e.Result = -2 Then Throw New DbSqlException("Unable to execute SQL string.", QuerySQL, e.Error)
        IsQueryBusy = False
        bwExecuteNonQuery.Dispose()
        bwExecuteNonQuery = Nothing
    End Sub

    ''' <summary>
    ''' Starts query on background thread
    ''' </summary>
    Private Sub bwExecuteScalar_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwExecuteScalar.DoWork
        QuerySQL = CType(e.Argument, String)
        IsQueryBusy = True
        e.Result = ExecuteScalar(QuerySQL)
    End Sub

    ''' <summary>
    ''' Called when background thread is complete
    ''' </summary>
    Private Sub bwExecuteScalar_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwExecuteScalar.RunWorkerCompleted
        RaiseEvent Progress(Me, 100, 100, "")
        IsQueryBusy = False
    End Sub

    Public Sub Cancel()
        dbCancelled = True
    End Sub

    ''' <summary>
    ''' This is called by inheritor's Overridden Clone method
    ''' </summary>
    ''' <param name="DB">Cloned instance of DB</param>
    Public Sub CloneSelf(ByRef DB As clsDB)
        With DB
            .CommandTimeout = dbTimeout
            .DatabaseName = dbDatabaseName
            .DatabaseType = dbType
            .DataSource = dbDataSource
            .Password = dbPassword
            .UserName = dbUserName
            .CommandTimeout = dbTimeout
            .CreateConnection()
        End With
    End Sub

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public MustOverride Sub CreateConnection()

    ''' <summary>
    ''' Create index in the database
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <param name="ColumnName">Name of column</param>
    Public Sub CreateIndex(TableName As String, ColumnName As String, Optional dtWorkTemplate As DataTable = Nothing)
        If TableExists(TableName) Then
            ExecuteNonQuery("CREATE INDEX {0}_{1} ON {2}({1})", TableName, ColumnName, FullTableName(TableName))
            Schema_IndexColumns = Nothing
            Schema_Indexes = Nothing
        End If
    End Sub

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
            End If
        End If
        Me.disposedValue = True
    End Sub

    ''' <summary>
    ''' Delete index in the database
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <param name="ColumnName">Name of column</param>
    Public Sub DropIndex(TableName As String, ColumnName As String, Optional dtTempTemplate As DataTable = Nothing)
        If TableExists(TableName) AndAlso IsTableIndexed(TableName) Then
            Select Case DatabaseType
                Case enumDBType.Firebird, enumDBType.Firebird_Embedded, enumDBType.SQLite
                    ExecuteNonQuery("DROP INDEX {0}_{1}", TableName, ColumnName)
                Case Else
                    ExecuteNonQuery("DROP INDEX {0}_{1} ON {2}", TableName, ColumnName, FullTableName(TableName))
            End Select
            Schema_Indexes = Nothing
            Schema_IndexColumns = Nothing
        End If
    End Sub

    ''' <summary>
    ''' Determine if current database user has table edit permissions (create, drop, alter); this is only done for client/server providers which might be used for Enterprise WRDB (to improve performance)
    ''' </summary>
    ''' <returns>True if can create/drop/alter table schema</returns>
    ''' <remarks>Don't call CreateTable, etc. routines so can avoid exception stops during testing</remarks>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Function HasTablePermissions() As Boolean
        Select Case dbType
            Case enumDBType.SQL_Server, enumDBType.Oracle, enumDBType.MySQL, enumDBType.Firebird
                If Not TestConnectionQuick(5) Then Return False
                Const TestTable As String = "WRDBTestTable"
                Try
                    If TableExists(TestTable) Then ExecuteNonQuery("DROP TABLE {0}", TestTable)
                    ExecuteNonQuery("CREATE TABLE {0} (TestCol VARCHAR(255))", TestTable)
                    Select Case DatabaseType
                        Case enumDBType.SQL_Server
                            ExecuteNonQuery("sp_RENAME '{0}','{0}2'", TestTable)
                        Case Else
                            ExecuteNonQuery("ALTER TABLE {0} RENAME TO {0}2", TestTable)
                    End Select
                    Return True
                Catch ex As Exception
                    Return False
                Finally
                    Try
                        Schema_Tables = Nothing
                        If TableExists(TestTable) Then DropTable(TestTable)
                        If TableExists(TestTable & "2") Then DropTable(TestTable & "2")
                    Catch ex As Exception
                    End Try
                End Try
            Case Else
                Return True
        End Select
    End Function

    ''' <summary>
    ''' Does this database type support views?
    ''' </summary>
    Public Function SupportsViews() As Boolean
        Select Case DatabaseType
            Case enumDBType.CSV, enumDBType.DBase, enumDBType.Excel, enumDBType.Paradox, enumDBType.Text, enumDBType.SQL_Server_CE
                Return False
            Case Else
                Return True
        End Select
    End Function

    ''' <summary>
    ''' Does this database type support subqueries?
    ''' </summary>
    Public Function SupportsSubqueries() As Boolean

        'actually, I think all do if properly formatted--must test again
        Return True

        Select Case DatabaseType
            Case enumDBType.MySQL, enumDBType.SQL_Server, enumDBType.SQL_Server_CE
                Return False
            Case Else
                Return True
        End Select
    End Function

    ''' <summary>
    ''' Delete table in the database (also drops view if it exists)
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Sub DropTable(TableName As String)
        DropView(TableName)
        If TableExists(TableName) Then
            Me.ExecuteNonQuery("DROP TABLE {0}", FullTableName(TableName))
            Schema_Tables = Nothing
            Schema_Columns = Nothing
            Schema_Indexes = Nothing
            Schema_IndexColumns = Nothing
        End If
    End Sub

    ''' <summary>
    ''' Delete view in the database
    ''' </summary>
    ''' <param name="ViewName">Name of view</param>
    Public Sub DropView(ViewName As String)
        If ViewExists(ViewName) Then
            'MySQL will report that a view exists but it really doesn't, so trap this
            Try
                Me.ExecuteNonQuery("DROP VIEW {0}", ViewName)
                Schema_Views = Nothing
                Schema_Columns = Nothing
                Schema_Indexes = Nothing
                Schema_IndexColumns = Nothing
            Catch ex As Exception
                LastException = ex
            End Try
        End If
    End Sub

    ''' <summary>
    ''' Will stop the fill operation if the user cancels (ignored by SQLite and gives error for Firebird)
    ''' </summary>
    Private Sub dt_RowChanged(sender As Object, e As System.Data.DataRowChangeEventArgs) Handles dt.RowChanged
        Static ctr As Integer = Integer.MinValue
        If ctr = Integer.MaxValue Then ctr = Integer.MinValue
        ctr += 1
        'If ctr Mod 1000 = 0 Then
        '    RaiseProgress(-1, 0, "Filling table...")
        'End If
        If dbCancelled AndAlso Not (dbType = enumDBType.Firebird OrElse dbType = enumDBType.Firebird_Embedded) Then db_da.SelectCommand.Cancel()
    End Sub

    Public Sub RaiseProgress(Value As Long, MaxValue As Long, ProgressText As String)
        RaiseEvent Progress(Me, Value, MaxValue, ProgressText)
    End Sub

    ''' <summary>
    ''' Perform database compaction/repair operation where supported; if not overridden assumes cannot be repaired
    ''' </summary>
    ''' <param name="Interactive">If true (and Paradox) will open repair utility for interactive use</param>
    Public Overridable Sub RepairDatabase(Optional Interactive As Boolean = False)
        Throw New DbSqlException("This data provider does not include a means to perform database compaction or repair. This function is provided by the database administrator using separate tools.")
    End Sub

    Public Property Cancelled() As Boolean
        Get
            Return dbCancelled
        End Get
        Set(value As Boolean)
            dbCancelled = value
        End Set
    End Property

    ''' <summary>Length of time (sec) for command to timeout and throw error</summary>
    Public Property CommandTimeout() As Integer
        Get
            If dbType = enumDBType.SQL_Server_CE Then
                Return 0
            Else
                Return dbTimeout
            End If
        End Get
        Set(Value As Integer)
            dbTimeout = Value
        End Set
    End Property

    ''' <summary>Length of time (sec) for command to timeout and throw error (only applies to a single query then resets itself)</summary>
    Public WriteOnly Property CommandTimeoutOverride() As Integer
        Set(Value As Integer)
            dbTimeoutOverride = Value
        End Set
    End Property

    ''' <summary>
    ''' Database connection object
    ''' </summary>
    Public Property Connection() As DbConnection
        Set(value As DbConnection)
            dbConnection = value
        End Set
        Get
            Return dbConnection
        End Get
    End Property

    ''' <summary>
    ''' Name of database on server
    ''' </summary>
    ''' <remarks>Not used for embedded databases--will return empty string</remarks>
    Public Property DatabaseName() As String
        Get
            Select Case Me.dbType
                Case enumDBType.Firebird, enumDBType.MySQL, enumDBType.Oracle, enumDBType.SQL_Server : Return dbDatabaseName
                Case Else
                    Return ""
            End Select
        End Get
        Set(Value As String)
            dbDatabaseName = Value
        End Set
    End Property

    ''' <summary>
    ''' List of data providers supported by WRDB
    ''' </summary>
    ''' <remarks>Only a subset of these are extensively tested</remarks>
    Public Overridable Property DatabaseType() As enumDBType
        Get
            Return dbType
        End Get
        Set(Value As enumDBType)
            dbType = Value
        End Set
    End Property

    ''' <summary>
    ''' String containing name of database file (for embedded databases) or server name (or IP address)
    ''' </summary>
    Public Property DataSource() As String
        Get
            Return dbDataSource
        End Get
        Set(Value As String)
            dbDataSource = Value
        End Set
    End Property

    ''' <summary>Password for protected databases</summary>
    Public Property Password() As String
        Get
            Return dbPassword
        End Get
        Set(value As String)
            dbPassword = value
        End Set
    End Property

    ''' <summary>User name for protected databases</summary>
    Public Property UserName() As String
        Get
            Return dbUserName
        End Get
        Set(value As String)
            dbUserName = value
        End Set
    End Property

    ''' <summary>
    ''' Capitalize first character and any characters following a space,|,\,;,.,_,(
    ''' If string is not all lowercase, will just return the original string
    ''' </summary>
    Public Function Proper(s As String) As String
        'If s <> s.ToLower Then Return s
        Dim ar() As Char = s.ToLower.ToCharArray
        For i As Integer = 0 To ar.Length - 1
            If i = 0 OrElse " |\;_(".Contains(ar(i - 1)) Then ar(i) = Char.ToUpper(ar(i))
        Next
        Return New String(ar)
    End Function

    ''' <summary>
    ''' Given real number string in culture-invariant format, return value (use in place of CDate)
    ''' </summary>
    ''' <param name="Number">Culture-invariant number format like 1,234.56</param>
    ''' <returns></returns>
    ''' <remarks>Useful when running in non-US culture</remarks>
    Public Function MyNumber(Number As String) As Double
        Return Double.Parse(Number, System.Globalization.CultureInfo.InvariantCulture)
    End Function

    ''' <summary>
    ''' Convert list of strings to comma-delimited string
    ''' </summary>
    ''' <param name="ar">String items to concatenate</param>
    ''' <param name="AddTextDelimiters">If true, single tick marks will surround each list item</param>
    Public Function ArrayToCSV(ar() As String, Optional AddTextDelimiters As Boolean = False) As String
        Dim csv As String = ""
        Dim delim As String = IIf(AddTextDelimiters, "'", "")
        For Each s As String In ar
            csv &= IIf(csv = "", "", ",") & delim & s.Trim & delim
        Next
        Return csv
    End Function

    ''' <summary>
    ''' Convert list of strings to comma-delimited string
    ''' </summary>
    ''' <param name="List">String items to concatenate</param>
    ''' <param name="AddTextDelimiters">If true, single tick marks will surround each list item</param>
    Public Function ArrayToCSV(List As Generic.List(Of String), Optional AddTextDelimiters As Boolean = False) As String
        Return ArrayToCSV(List.ToArray, AddTextDelimiters)
    End Function

    Public Enum enumSQLType
        Scalar
        Reader
        NonQuery
    End Enum

    Public Function ExecuteSQL(SQLType As enumSQLType, ProgressText As String, SQLString As String, ParamArray Args() As Object) As Object
        Dim Result As Object = Nothing
        If DatabaseType = enumDBType.SQL_Server_CE Then 'does not handle multithreaded correctly
            Select Case SQLType
                Case enumSQLType.Scalar
                    Result = ExecuteScalar(SQLString, Args)
                Case enumSQLType.Reader
                    Result = ExecuteReader(SQLString, Args)
                Case enumSQLType.NonQuery
                    Result = ExecuteNonQuery(SQLString, Args)
            End Select
        Else
            Dim sw As New Stopwatch
            sw.Start()
            Dim t As New Threading.Thread(
            Sub()
                Try
                    Select Case SQLType
                        Case enumSQLType.Scalar
                            Result = ExecuteScalar(SQLString, Args)
                        Case enumSQLType.Reader
                            Result = ExecuteReader(SQLString, Args)
                        Case enumSQLType.NonQuery
                            Result = ExecuteNonQuery(SQLString, Args)
                    End Select
                Catch ex As Exception
                    Throw
                End Try
            End Sub
            )
            t.IsBackground = True
            t.Start()
            RaiseProgress(0, 10, ProgressText)
            While sw.Elapsed.Seconds < CommandTimeout
                If t.Join(1) Then Exit While
                'RaiseProgress(sw.Elapsed.Seconds Mod 10, 10, ProgressText) 'let it cycle every ten seconds
                RaiseProgress(-1, 10, ProgressText) 'marquee
                If Cancelled Then t.Abort() : Exit While
            End While
            sw.Stop()
            RaiseProgress(10, 10, ProgressText)
        End If
        Return Result
    End Function

    '''' <summary>
    '''' Parse a simple SELECT SQL statement to return the field list and WHERE clause (if it exists)
    '''' </summary>
    '''' <param name="SQL">SELECT SQL string</param>
    '''' <param name="FieldList">List of fields selected</param>
    '''' <param name="WhereClause">If found, returns WHERE clause (including keyword)</param>
    '''' <param name="TableName">Name of table referenced in statement</param>
    '''' <returns></returns>
    '''' <remarks>Format must be SELECT (field list) FROM (tablename) [WHERE (conditions)]</remarks>
    'Public Shared Function ParseSimpleSelectStmt(SQL As String, ByRef FieldList As String, ByRef WhereClause As String, ByRef TableName As String) As Boolean
    '    Try
    '        If String.IsNullOrEmpty(SQL) OrElse Not SQL.ToUpper.StartsWith("SELECT") Then Return False
    '        FieldList = ""
    '        WhereClause = ""
    '        TableName = ""
    '        Dim IsInFieldList As Boolean = False
    '        Dim IsInWhereClause As Boolean = False
    '        Dim IsInTablename As Boolean = False
    '        For Each s As String In SQL.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries)
    '            Select Case s.ToUpper
    '                Case "SELECT"
    '                    IsInFieldList = True
    '                Case "INTO", "GROUP", "HAVING"
    '                    IsInFieldList = False
    '                    IsInWhereClause = False
    '                Case "FROM"
    '                    IsInFieldList = False
    '                    IsInWhereClause = False
    '                    IsInTablename = TableName = "" 'may be a from in where clause (EXTRACT...)
    '                Case "WHERE"
    '                    IsInFieldList = False
    '                    IsInWhereClause = True
    '                    IsInTablename = False
    '                Case Else
    '                    If IsInFieldList Then FieldList &= IIf(FieldList = "", "", " ") & s
    '                    If IsInWhereClause Then WhereClause &= IIf(WhereClause = "", "WHERE ", " ") & s
    '                    If IsInTablename Then
    '                        TableName &= IIf(TableName = "", "", " ") & s
    '                        If TableName.StartsWith("[") Or TableName.StartsWith("""") Then 'get next items until find closing bracket
    '                            IsInTablename = Not (TableName.EndsWith("]") Or TableName.EndsWith(""""))
    '                        ElseIf TableName.StartsWith("(") Then 'is subselect; place entire thing in tablename
    '                            Dim NumLeftParens As Integer = 0
    '                            Dim NumRightParens As Integer = 0
    '                            For Each c As Char In TableName
    '                                If c = "(" Then NumLeftParens += 1
    '                                If c = ")" Then NumRightParens += 1
    '                            Next
    '                            IsInTablename = NumLeftParens > NumRightParens 'end of subselect has been found when they are equal
    '                        Else
    '                            IsInTablename = False
    '                        End If
    '                    End If
    '            End Select
    '        Next
    '        Return True
    '    Catch ex As Exception
    '        Return False
    '    End Try
    'End Function

    '''' <summary>
    '''' Parse a simple SELECT SQL statement to return the WHERE clause (if it exists)
    '''' </summary>
    '''' <param name="SQL">SELECT SQL string</param>
    '''' <returns>If found, returns WHERE clause (including keyword)</returns>
    '''' <remarks></remarks>
    'Public Shared Function GetWhereClause(SQL As String) As String
    '    Dim FieldList As String = "", WhereClause As String = "", TableName As String = ""
    '    If ParseSimpleSelectStmt(SQL, FieldList, WhereClause, TableName) Then Return WhereClause Else Return ""
    'End Function

    '''' <summary>
    '''' Parse a simple SELECT SQL statement to return the table name
    '''' </summary>
    '''' <param name="SQL">SELECT SQL string</param>
    '''' <returns>Name of table</returns>
    '''' <remarks></remarks>
    'Public Shared Function GetTableName(SQL As String) As String
    '    Dim FieldList As String = "", WhereClause As String = "", TableName As String = ""
    '    If ParseSimpleSelectStmt(SQL, FieldList, WhereClause, TableName) Then Return TableName Else Return ""
    'End Function

End Class

Public Class DbSqlException
    Inherits Exception


    Public Sub New()
        MyBase.New()
    End Sub

    Public Sub New(ErrorMsg As String)
        MyBase.New(ErrorMsg)
    End Sub

    Public Sub New(ErrorMsg As String, ByRef ex As Exception)
        MyBase.New(TimeoutMsg(ex) & ErrorMsg, ex)
    End Sub

    Public Sub New(ErrorMsg As String, SQLString As String)
        MyBase.New(String.Format("{0}\n\nThe SQL string was:\n\n{1}", ErrorMsg, FormatSQL(SQLString)))
    End Sub

    Public Sub New(ErrorMsg As String, SQLString As String, ByRef ex As Exception)
        MyBase.New(String.Format("{0}\n\n" & IIf(SQLString = "", "", String.Format("The SQL string was:\n\n{0}\n\n", FormatSQL(SQLString))) & "The detailed error was:\n\n{1}", TimeoutMsg(ex) & ErrorMsg, ex.Message), ex)
    End Sub

    Shared Function TimeoutMsg(ex As Exception) As String
        Do
            If ex.Message.ToLower.Contains("timeout") Then Return "Warning! A database timeout error occurred. You can increase the command timeout setting under Project Preferences to allow longer queries to execute without error. In addition, consider adding indices to your data tables to speed up queries.\n\n"
            ex = ex.InnerException
        Loop Until ex Is Nothing
        Return ""
    End Function
End Class

''' <summary>
''' This is a poor-man's SQL Parser; will only work with selected simple queries
''' </summary>
Public Class clsSQLParser

    Private SQLString As String = Nothing
    Private SQLTokens() As String = Nothing
    Private _LastParseError As String = ""

    ''' <summary>Comma-separated list of fields</summary>
    Public Property Fields As String = ""
    ''' <summary>Full table reference including alias and JOINS or subselect string with alias</summary>
    Public Property From As String = ""
    ''' <summary>First tablename in table reference or subselect</summary>
    Public Property Table As String = ""
    ''' <summary>Contents of WHERE clause</summary>
    Public Property Where As String = ""
    ''' <summary>Contents of ORDER BY clause</summary>
    Public Property OrderBy As String
    ''' <summary>Contents of GROUP BY clause</summary>
    Public Property GroupBy As String
    ''' <summary>Contents of HAVING clause</summary>
    Public Property Having As String
    ''' <summary>If ParseSelect returns false this explains why</summary>
    Public Property LastParseError As String
        Set(value As String)
            _LastParseError = value
            ParseErrors.Add(_LastParseError)
        End Set
        Get
            Return _LastParseError
        End Get
    End Property

    ''' <summary>If ParseSelect returns false this explains why</summary>
    Public Property ParseErrors As New List(Of String)

    Public Enum enumQueryType
        Create
        Delete
        Drop
        Insert
        [Select]
        Update
        Unknown
        ParseError
    End Enum

    Public Sub New(SQLString As String)
        Me.SQLString = SQLString
        SQLTokens = SQLString.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries)
    End Sub

    ''' <summary>
    ''' Return type of of SQL statement or parsing error info
    ''' </summary>
    ''' <returns>Use LastParseError to learn details if return PareseError</returns>
    Public Function QueryType() As enumQueryType
        If SQLTokens.Length = 0 Then
            Return enumQueryType.ParseError
        Else
            Select Case SQLTokens(0).ToUpper
                Case "CREATE" : Return enumQueryType.Create
                Case "DELETE" : Return enumQueryType.Delete
                Case "DROP" : Return enumQueryType.Drop
                Case "INSERT" : Return enumQueryType.Insert
                Case "SELECT" : Return enumQueryType.Select
                Case "UPDATE" : Return enumQueryType.Update
                Case Else
                    Return enumQueryType.Unknown
            End Select
        End If
    End Function

    ''' <summary>
    ''' Parse the select query and populate the clause contents as appropriate
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>Will strip off all clause identifiers (e.g., WHERE); ORDER BY and GROUP BY must only have one space separating</remarks>
    Public Function ParseSelect() As Boolean
        If QueryType() <> enumQueryType.Select Then Return False
        Dim tokennum As Integer = 0
        While tokennum <= SQLTokens.Length - 1
            Select Case SQLTokens(tokennum).ToUpper
                Case "SELECT"
                    Fields = ParsePhrase(tokennum, New String() {"DISTINCT", "UNIQUE", "ALL"}, New String() {"FROM"})
                    If Fields = "" Then
                        LastParseError = "Unable to parse field list."
                        Return False
                    End If
                Case "INTO"
                    LastParseError = "SELECT INTO queries are not supported."
                Case "FROM"
                    From = ParsePhrase(tokennum, Nothing, New String() {"WHERE", "GROUP", "HAVING", "ORDER", "UNION"})
                    If From <> "" Then
                        If From.StartsWith("(") Then 'strip off everything after last )
                            Table = From.Substring(0, From.IndexOf(")") + 1)
                        ElseIf From.StartsWith("[") Then
                            Table = From.Substring(0, From.IndexOf("]") + 1)
                        ElseIf From.StartsWith("""") Then
                            Table = From.Substring(0, From.IndexOf("""") + 1)
                        Else 'assume table is not delimited; just strip off last alias if it exists 
                            Dim ar() As String = From.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries)
                            If ar.Length <= 2 Then
                                Table = ar(0)
                            Else
                                LastParseError = "Unable to parse table in FROM list: " & From
                                Return False
                            End If
                        End If
                    Else
                        LastParseError = "Unable to parse FROM list."
                        Return False
                    End If
                Case "WHERE"
                    Where = ParsePhrase(tokennum, Nothing, New String() {"GROUP", "HAVING", "ORDER", "UNION"})
                Case "GROUP"
                    tokennum += 1 'skip BY
                    GroupBy = ParsePhrase(tokennum, Nothing, New String() {"HAVING", "ORDER", "UNION"})
                Case "HAVING"
                    Having = ParsePhrase(tokennum, Nothing, New String() {"UNION"})
                Case "ORDER"
                    tokennum += 1 'skip BY
                    GroupBy = ParsePhrase(tokennum, Nothing, New String() {"UNION"})
            End Select
            tokennum += 1
        End While
        Return True
    End Function

    ''' <summary>
    ''' Extract next portion of SQL string up until specified keyword if found or end of string
    ''' </summary>
    ''' <param name="tokenNum">Token index (positioned at token like FROM, WHERE, etc)</param>
    ''' <param name="IgnoredTokens">List of tokens that will be ignored (must be upper case)</param>
    ''' <param name="FollowingTokens">List of tokens that can terminate phrase (must be upper case)</param>
    ''' <returns>Phrase</returns>
    Private Function ParsePhrase(ByRef tokenNum As Integer, IgnoredTokens As String(), FollowingTokens As String()) As String
        Dim phrase As String = ""
        Dim lstIgnoredTokens As New List(Of String)
        If IgnoredTokens IsNot Nothing Then lstIgnoredTokens.AddRange(IgnoredTokens)
        Dim lstFollowingTokens As New List(Of String)
        If FollowingTokens IsNot Nothing Then lstFollowingTokens.AddRange(FollowingTokens)

        Dim parenLevel As Integer = 0

        Do
            tokenNum += 1
            For Each s As String In lstIgnoredTokens
                If String.Equals(s, SQLTokens(tokenNum), StringComparison.InvariantCultureIgnoreCase) Then Continue Do
            Next

            If Not lstIgnoredTokens.Contains(SQLTokens(tokenNum).ToUpper) Then
                phrase &= IIf(phrase = "", "", " ") & SQLTokens(tokenNum)
            End If

            If tokenNum >= SQLTokens.Length - 1 Then Return phrase

            'if need to parse everything in a subselect, keep track of paired parens
            If SQLTokens(tokenNum).Contains("(") Then parenLevel += 1
            If SQLTokens(tokenNum).Contains(")") Then parenLevel -= 1

            If lstFollowingTokens.Contains(SQLTokens(tokenNum + 1).ToUpper) AndAlso parenLevel = 0 Then Return phrase

        Loop
    End Function

End Class

'Public Class DbParser
'    Private _sqlstring As String = Nothing
'    Private _parser As New TSql100Parser(False)
'    Public Fields, From, Where, OrderBy, GroupBy, Having As String
'    Public parseErrors As Generic.IList(Of ParseError) = Nothing

'    Public Function LastParseError() As String
'        If parseErrors.Count = 0 Then Return ""
'        Dim s As String = ""
'        For Each p As ParseError In parseErrors
'            s &= p.Message & ": Line " & p.Line & "; Column " & p.Column
'        Next
'        Return s
'    End Function

'    Public Enum enumQueryType
'        [Select]
'        Unknown
'        [ParseError]
'    End Enum

'    Public Sub New(SQLString As String)
'        _sqlstring = SQLString
'    End Sub

'    ''' <summary>
'    ''' Return type of of SQL statement or parsing error info
'    ''' </summary>
'    ''' <returns>Use LastParseError to learn details if return PareseError</returns>
'    Public Function QueryType() As enumQueryType
'        Dim script As TSqlScript = _parser.Parse(New StringReader(_sqlstring), parseErrors)
'        If parseErrors.Count > 0 Then Return enumQueryType.ParseError
'        Dim stmt As TSqlStatement = script.Batches(0).Statements(0)
'        If TypeOf stmt Is SelectStatement Then
'            Return enumQueryType.Select
'        Else
'            Return enumQueryType.Unknown
'        End If
'    End Function

'    ''' <summary>
'    ''' Parse the select query and populate the clause contents as appropriate
'    ''' </summary>
'    ''' <returns></returns>
'    ''' <remarks>Will strip off all clause identifiers (e.g., WHERE); ORDER BY and GROUP BY must only have one space separating</remarks>
'    Public Function ParseSelect() As Boolean
'        Dim script As TSqlScript = _parser.Parse(New StringReader(_sqlstring), parseErrors)
'        If parseErrors.Count > 0 Then Return False

'        Dim stmt As SelectStatement = script.Batches(0).Statements(0)

'        'if UNION query will fail to parse
'        If TypeOf (stmt.QueryExpression) IsNot QuerySpecification Then Return False

'        Dim query As QuerySpecification = stmt.QueryExpression

'        Dim lst As New List(Of String)

'        For Each elem As SelectColumn In query.SelectElements
'            lst.Add(GetString(elem))
'        Next
'        Fields = String.Join(",", lst)

'        lst.Clear()
'        For Each elem As TableSource In query.FromClauses
'            lst.Add(GetString(elem))
'        Next
'        From = String.Join(",", lst)

'        Where = GetString(query.WhereClause)
'        Where = TestNull(Replace(Where, "where", "",,, CompareMethod.Text), "").Trim

'        OrderBy = GetString(stmt.OrderByClause)
'        OrderBy = TestNull(Replace(OrderBy, "order by", "",,, CompareMethod.Text), "").Trim

'        GroupBy = GetString(query.GroupByClause)
'        GroupBy = TestNull(Replace(GroupBy, "group by", "",,, CompareMethod.Text), "").Trim

'        Having = GetString(query.HavingClause)
'        Having = TestNull(Replace(Having, "having", "",,, CompareMethod.Text), "").Trim

'        Return True
'    End Function

'    Private Function GetString(statement As TSqlFragment) As String
'        Dim s As String = String.Empty
'        If statement Is Nothing Then Return String.Empty
'        For i As Integer = statement.FirstTokenIndex To statement.LastTokenIndex
'            s &= statement.ScriptTokenStream(i).Text
'        Next
'        Return s
'    End Function

'End Class